I've below Table in Postgres and using Java 8 and Postgres-11 with Spring Boot 2.1.6.RELEASE. I already went through this question and this question, but I really wanted to use Java 8 Date API and not Java 7.
CREATE TABLE note(
note_id serial PRIMARY KEY,
message varchar(255) NOT NULL,
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
I've created model class like below, but doesn't match with what I need.
@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
@EqualsAndHashCode(callSuper = true)
@Entity
public class Note extends AuditEnabledEntity{
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "note_id")
private int noteId;
@Column(name = "message")
private String message;
@Column(name = "created_at")
private LocalDateTime createdAt;
}
LocalDateTime
is the wrong type here. That class cannot represent a moment, as explained in its Javadoc.
That class purposely has no concept of time zone or offset-from-UTC. So it represents a date and a time-of-day such as “noon on the 23rd of January 2019”, but don’t know if that is noon in Tokyo, Paris, or Montréal, for a example, three very different moments that are several hours apart. So this type is appropriate for standard SQL type TIMESTAMP WITHOUT TIME ZONE
– without, not with.
For more discussion, see: What's the difference between Instant and LocalDateTime?
For standard SQL type TIMESTAMP WITH TIME ZONE
, you should be using the Java types Instant
, OffsetDateTime
, or ZonedDateTime
. Of those three, JDBC 4.2 requires support only for the second, OffsetDateTime
.
Retrieval.
OffsetDateTime odt = myResultSet.getObject( … , OffsetDateTime.class ) ;
The value retrieved from Postgres will always be in UTC. The SQL standard does not specify this behavior, so databases vary. In Postgres any value sent to a field of type TIMESTAMP WITH TIME ZONE
is adjusted into UTC. Retrieved values are in UTC.
Storing.
myPreparedStatement.setObject( … , odt ) ;
Adjust from UTC (offset of zero) to the wall-clock time used by the people of a particular region (a time zone).
ZoneId z = ZoneId.of( "Asia/Tokyo" ;
ZonedDateTime zdt = odt.atZoneSameInstant( z ) ;
I don’t use JPA, preferring to keep things simple.
But according to this Answer, JPA 2.2 support the java.time types.
Hibernate as well supports java.time.