JPA Model Class for field TIMESTAMP WITHOUT TIME ZONE DEFAULT CURRENT_TIMESTAMP in Postgres?

Question

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;
}

Answer 1

Wrong type

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?

Right type

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 ) ;

JPA

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.