Hibernate - Using java.time.Instant with PostgreSQL

Question

I get a SchemaManagementException because of a type mismatch. The Hibernate version is 5.2.8.Final. The hibernate.ddl-auto is set to validate

org.hibernate.tool.schema.spi.SchemaManagementException: Schema-validation: wrong column type encountered in column [end_date] in table [certificate]; found [date (Types#DATE)], but expecting [timestamp (Types#TIMESTAMP)]

The column entity is of type java.time.Instant, and the PostgreSQL column is of type TIMESTAMPTZ.

@Column(name = "end_date")
private Instant endDate;

For java.time.Instant, the Hibernate type is InstantType which maps to a TIMESTAMP JDBC type. So I understand why the error says that it's expecting a Types#TIMESTAMP. What I don't understand is that the error says that it found a Types#DATE.

Source: https://docs.jboss.org/hibernate/orm/5.2/userguide/html_single/Hibernate_User_Guide.html

One workaround is to set the column definition as

@Column(name = "end_date", columnDefinition="DATE")
private Instant endDate;

but I am not really convinced it's a solution. To me an Instant is a TIMESTAMP, not a DATE.

Sydney · Accepted Answer · 2017-09-02 19:34:13Z

6

The workaround is not the solution. For java.time.Instant, the PostgreSQL column should be of type TIMESTAMP. It was a mistake in the DB creation.

answered Sep 2, 2017 at 19:34

Sydney

12.3k21 gold badges94 silver badges146 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

augurar Over a year ago

TIMESTAMP corresponds to LocalDateTime, not Instant.

Sydney Over a year ago

You're wrong see docs.jboss.org/hibernate/orm/5.2/userguide/html_single/…

augurar Over a year ago

A PostgreSQL TIMESTAMP Java 8 LocalDateTime represent a date and time with no time zone information. By contrast, PostgreSQL TIMESTAMPTZ and Java 8 Instant represent an absolute point in time whose calendar date and wall clock time depends on the time zone. These are fundamentally different concepts. You can convert between them if you know the time zone, which is what Hibernate does, but they are not the same thing.

Teocali Over a year ago

Given that an instant is ALWAYS stored in UTC, it make sense to use TIMESTAMP and not TIMESTAMPZ. Because a TIMESTAMPZ will be automatically modified if you change the timezone of your database.

Dave Cramer Over a year ago

This isn't quite true. TimestampTZ changes based on the timezone of the client, not the database.

Collectives™ on Stack Overflow

Hibernate - Using java.time.Instant with PostgreSQL

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related