Hibernate with Java 8 LocalDate & LocalDateTime in Database

Question

My requirement is to store all dates & date-times in UTC timezone in the database. I am using Java 8's LocalDate & LocalDateTime in my Hibernate entities.

Is that correct as LocalDate & LocalDateTime doesn't have timezone associated with them?

If not, should I fall back to using good old (or legacy?) Date & Timestamp?

Or should I be using Java 8's Instant? If using Instant, will there be a possibility to store only the date part, without time?

The database is MySQL & SQL Server and this is a Spring Boot application.

Answer 1

The “Local…” types purposely have no concept of time zone. So they do not represent a moment on the timeline. A LocalDateTime represents a vague range of possible moments but has no real meaning until assigning an offset or time zone. That means applying a ZoneId to get a ZonedDateTime.

For example, to say that Christmas this year starts at the first moment of December 25, we say:

LocalDateTime ldt = LocalDateTime.of( 2017 , 12 , 25 , 0 , 0 , 0 , 0 );

But that stroke of midnight happens earlier in the east than in the west.

That is why the elves’ Logistics department maps out Santa’s route starting at Kiribati in the Pacific, the earliest time zone in the world at 14 hours ahead of UTC. After delivering there, they route Santa westward to places like New Zealand for its midnight later. Then on to Asia for their midnight later. Then India, and so on, reaching Europe for their midnight several hours later, and then the east coast of North America for their midnight a few hours more later. All of these places experienced that same LocalDateTime at different moments, each delivery represented by a different ZonedDateTime object.

So…

• If you want to record the concept of Christmas starting after midnight on the 25th, use a LocalDateTime and write into a database column of type TIMESTAMP WITHOUT TIME ZONE.
• If you want to record the exact moment of each delivery Santa makes, use a ZonedDateTime and write into a database column of type TIMESTAMP WITH TIME ZONE.

About that second bullet, be aware that nearly every database system will use the zone information to adjust the date-time to UTC and store that UTC value. Some save the zone information as well, but some such as Postgres discard the zone info after using it to adjust into UTC. So “with time zone” is something of a misnomer, really meaning “with respect for time zone”. If you care about remembering that original zone, you may need to store its name in a separate column alongside.

Another reason to use Local… types is for future appointments. Politicians enjoy frequently changing their time zone(s) of their jurisdiction. They like to adopt Daylight Saving Time (DST). The like to change the dates of their DST cutovers. They like to drop their adoption of DST. They like to redefine their time zones, changing the boundaries. They like to redefine their offset-from-UTC sometimes by amounts like 15 minutes. And they rarely give advance notice, making such changes with as little as a month or two of warning.

So to make medical check-up appointment for next year or in six months, the time zone definition cannot be predicted. So if you want an appointment of 9 AM, you should use a LocalTime or LocalDateTime recorded in a database column of type TIMESTAMP WITHOUT TIME ZONE. Otherwise that 9 AM appointment, if zoned where the DST cutover is postponed, may appear as 8 AM or 10 AM.

When generating a projected schedule, you can apply a time zone (ZoneId) to those “local” (unzoned) values to create ZonedDateTime objects. But do not rely on those too far out in time when politicians may ruin their meaning by changing the zone(s).

Tip: These frequent changes to DST and time zones means you must keep your time zone tzdata data base up to date. There is a tzdata in your host OS, your JVM, and perhaps in your database system such as Postgres. All three should be frequently updated. Sometimes the zones change faster than the planned update cycles of those products such as Turkey last year deciding to stay on DST with only several weeks notice. So you may occasionally need to manually update those tzdata files. Oracle provides a tool for updating the tzdata of their Java implementations.

The general best practice in handling exact moments is to track them in UTC. Apply a time zone only where necessary such as in presentation to a user where they expect to see values in their own parochial time zone. In java.time, the Instant class represents a moment in the timeline. In UTC with a resolution of nanoseconds.

Instant instant = Instant.now() ;  // Current moment on the timeline in UTC.
ZonedDateTime zdt = instant.atZone( z ) ;  // Assign a time zone to view the same moment through the lens of a particular region’s wall-clock time.
Instant instant = zdt.toInstant();  // revert back to UTC, stripping away the time zone. But still the same moment in the timeline.

By the way, drivers that comply with JDBC 4.2 and later can deal directly with the java.time types via:

• PreparedStatement::setObject
• ResultSet::getObject

Oddly, the JDBC 4.2 spec does not require support for the two most common java.time types: Instant & ZonedDateTime. The spec does require support for OffsetDateTime. So you can easily convert back-and-forth.

Avoid the old legacy data types such as java.util.Date and java.sql.Timestamp whenever possible. They are poorly designed, confusing, and flawed.

Understand that all of these four are representations of a moment on the timeline in UTC:

• Modern
  • java.time.Instant
  • java.time.OffsetDateTime with an assigned offset of ZoneOffset.UTC
• Legacy
  • java.util.Date
  • java.sql.Timestamp

If you want a date-only value without time-of-day and without time zone, use java.time.LocalDate. This class supplants java.sql.Date.

As for specific databases, be aware that the SQL standard barely touches on the topic of date-time types and their handling. Furthermore, the various databases vary widely, and I really mean widely, in their support of date-time features. Some have virtually no support. Some mix SQL standard types with proprietary types that either predate the standard types or are intended as alternatives to the standard types. In addition, JDBC drivers differ in their behavior with marshaling date-time values to/from the database. Be sure to study the documentation and practice, practice, practice.