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.

Answer 2

2023 answer using Spring Boot 3.1 (implies Hibernate 6.2)

Your Java class should be using Instant. Like this:

@Column(name = "createdAt", columnDefinition="TIMESTAMP WITH TIME ZONE")
private Instant createdAt;

Hibernate will notice that you want to map to an Instant and for such relation it will act as if the hibernate.jdbc.time_zone property is set to UTC (meaning you don't have to set this property). The methodology is safe to use meaning it will work consistently regardless of settings on the database server side or client side settings such as the default TZ of your own JVM. All these things are irrelevant when doing what I propose. And indeed you would want a methodology which is immune to such settings.

In the vast majority of cases what you really want to persist and read back is indeed an Instant (not any of the other java.time types). If you use say OffsetDateTime or ZonedDateTime in the persistence layer of your application then you give yourself an illusion that your persistence engine (in this case PostgreSQL) can do something which it really cannot.

Which database column type to use?

PostgreSQL doesn't store any time zone information with datatype TIMESTAMP WITH TIME ZONE so it doesn't make any difference for your Java application if your database column uses the TIMESTAMP WITH TIME ZONE type or the TIMESTAMP WITHOUT TIME ZONE type. I would probably still use TIMESTAMP WITH TIME ZONE as the values are a bit easier to interpret when I browse my table with some SQL browser tool. If you want to use an ANSI SQL type then just use TIMESTAMP which is portable across database platforms (PostgreSQL will interpret it as TIMESTAMP WITHOUT TIME ZONE). But again, for your Java application it is utterly irrelevant which of the two you use.

What about standards compliance?

The use of Instant was somehow forgotten in the JPA Spec, but it seems it will finally find its way into the JPA 3.2 Spec. The reason why my example code currently works is because of Hibernate, not because of JPA. Still, I wouldn't think twice about using Instant.

Can we never actually truly store a timestamp with timezone value?

A timezone specification can be either an offset (say +02:00) or a timezone region id (say Europe/Paris). First, you should realize that for everything that has already happened you might as well simply use an Instant. And the vast majority of use cases for timestamps in software are indeed for something which is in the past.

Offsets are in most cases pretty irrelevant to store as the database engine might as well simply first convert to the UTC offset (+00:00) and then store that value. You would not lose any information by doing that. There is no problem in throwing away the offset.

Timezone region ids are another matter. They do make a lot of sense to persist, but only for things in the future, say for example for long financial contracts ("last payment to be made no later than 12:00 noon Europe/Paris time, 1st of Jan, 2053") or for scheduling information (run this job Mondays at 08:00 am, Europe/Paris time). However, PostgreSQL column types cannot persist such information. Neither can MS SQL Server or MySQL. I only know of Oracle Database which can be do this. If you really have such need in PostgreSQL then you would need to use two columns and then have your Java code collectively view them as one value of type ZonedDateTime.

But your question is clearly about timestamps in the past ('created_at'), so your use case is simple: it is an Instant!