TL;DR Summary
Java Type | DB Type | Description | |
---|---|---|---|
SAVE | Long | NUMBER | Just using a long as milliseconds, between the current time and midnight, January 1, 1970 UTC is the most secure way. |
SAVE | Instant | NUMBER | Using in Java an Instant and converting it into a NUMBER to save it in the DB will also avoid any issues you might encounter. APIs using the RFC3339 format usually also avoids any problems. |
CONFIGURE | Instant | TIMESTAMP | Ensure you set your JDBC driver to UTC, as it might cause issues saving and loading the date. e.g.: spring.jpa.properties.hibernate.jdbc.time_zone=UTC |
AVOID | OffsetDateTime | TIMESTAMP | If an offset time zone is used, ensure the DB type support the time zone too. |
SAVE | OffsetDateTime | TIMESTAMP with timezone | A time stamp with zone e.g. timestampz in PostgreSQL combined with an OffsetDateTime is usually a save way to go too. |
AVOID | LocalDateTime | TIMESTAMP | Avoid the usage of local date or local date time. if this is not possible use always a time zone and set it in the jdbc driver e.g.:ZonedDateTime.now(ZoneOffset.UTC) spring.jpa.properties.hibernate.jdbc.time_zone=UTC -Duser.timezone=UTC |
Problem
Working with dates and a DB usually tends to create issues:
- Which type to use?
- Which type to save in the DB?
- How to handle time zones?
- How to avoid moving times?
- How to show the time in the correct time zone of the user?
Scenario
Our test entity
@Entity @Data public class Person { @Id private Long id; private long longDate; private Instant instantDate; private LocalDateTime localDateTime; private OffsetDateTime offsetDateTime; @Column(columnDefinition = "timestamptz") // we use here postgresql private OffsetDateTime offsetDateTimeZone; }
Given
- We use the entity above
- We use a PostgreSQL DB
- Service instance 1 accesses the DB using the time zone GMZ / UTC
- Service instance 2 accesses the DB using the time zone GMZ + 1
- We have one or more users with direct DB access from their local computer using the time zone Europa/Berlin
- We have currently winter time as it is october
When
- Service instance 1 write a record into the DB
// Instance 1: GMT WRITE Person( longDate = 1507751058820, instantDate = 2017-10-11T19:44:18.820418Z, localDateTime = 2017-10-11T19:44:18.969400, offsetDateTime = 2017-10-11T19:44:18.969438Z, offsetDateTimeZone = 2017-10-11T19:44:18.969455Z )
Then
- Service Instance 2 reads the data
-- Instance 2: GMT+01:00 READ Person( longDate = 1507751058820, // correct instantDate = 2017-10-11T18:44:18.820418Z, // wrong by one hour localDateTime = 2017-10-11T19:44:18.969400, // wrong by one hour as local time zone is +1 offsetDateTime = 2017-10-11T19:44:18.969438+01:00, // wrong by one hour too late offsetDateTimeZone = 2017-10-11T20:44:18.969455+01:00 // correct )
- A user looks into the DB
Only the timestampz
, number and instant column value looks okay. Storing an offset date into a type which doesn’t support the time zone leads here to an error.
Change the JDBC session to UTC
If the JDBC session is adjusted to use UTC instead of the JVM time zone using e.g. spring.jpa.properties.hibernate.jdbc.time_zone=UTC
we end up with the following result:
// Instance 1: GMT WRITE Person( longDate = 1507751058820, instantDate = 2017-10-11T19:44:18.820418Z, localDateTime = 2017-10-11T19:44:18.969400, offsetDateTime = 2017-10-11T19:44:18.969438Z, offsetDateTimeZone = 2017-10-11T19:44:18.969455Z ) -- Instance 2: GMT+01:00 READ Person( longDate = 1507751058820, // correct instantDate = 2017-10-11T19:44:18.820418Z, // now correct localDateTime = 2017-10-11T20:44:18.969400, // now also correct offsetDateTime = 2017-10-11T20:44:18.969438+01:00, // now also correct offsetDateTimeZone = 2017-10-11T20:44:18.969455+01:00 // now also correct )
Now all dates are okay, if all our instances really work with the same JDBC session time zone.
Avoid any time Zones in the business logic
Mathematically the time zone isn’t relevant assuming any date in the backend is saved and transferred as UTC date time with now time zone at all.
Save the date time as UTC long
, which is anyway the simplest method to compare dates to each other. (Means as milliseconds, between the current time and midnight, January 1, 1970 UTC.)
class Pojo { String foo; long dateTimeInMs = System.currentTimeMillis(); long dateTimeInS = Instant.now().getEpochSecond(); }
Even in the DB using a long / NUMBER has several advantages
- Any direct usage of the DB itself is hindered
- Date Time conversion by the JDBC driver is avoided
- A time zone cannot be introduced or assumed
- Clear also in external APIs using the Service
- The precision can be selected based on the domain, in most use cases seconds is enough
Instant in Java 8 or later
A valid alternative solution is to use Java Instant
, which is in the end a wrapped UTC long
and provides some further methods. It can be saved either as NUMBER
or TIMESTAMP
in the DB. But as soon as a real date type is used we have also to provide a time zone information in our JDBC URL e.g.:
- e.g. using
serverTimezone=UTC
- or
spring.jpa.properties.hibernate.jdbc.time_zone=UTC
OffsetDateTime
Many projects have chosen an offset date time type to avoid any issues with the time zone. The problem is, that it usually doesn’t address the issue if for some reason the time zone of your system changes.
Running save and load in different Time zones
Avoid Local Date Time
Local date time tends to surprise the people each time, even if it states, that the e.g. LocalDate
represents a date without a time zone, same applies to LocalDateTime
, it uses behind the scenes the system time zone:
public static LocalDate now() { return now(Clock.systemDefaultZone()); } public static LocalDateTime now() { return now(Clock.systemDefaultZone()); }
Which leads to the problem, that we have a time which was build using a time zone which is lost as soon as we have only the object. The value depends on the OS time zone which leads to interesting problems in a hosted server environment. Imagine e.g. that one node of the cluster has a different time zone because of some configuration issue in the infrastructure.
Having just the following simple test let us review the results, take in mind that my local time zone would be GMT+2 but currently we have wintertime, GMT+1.
@ParameterizedTest @ValueSource(strings = {"", "GMT+1", "GMT+6"}) void test(String timeZone) { if (timeZone.length() != 0) { TimeZone.setDefault(TimeZone.getTimeZone(timeZone)); } System.out.println(java.util.TimeZone.getDefault()); System.out.println("Instant: " + Instant.now()); System.out.println("LocalDateTime: " + LocalDateTime.now()); System.out.println("OffsetDateTime: " + OffsetDateTime.now()); }
Already this very simple example generates here with LocalDateTime
three different results. Even if the offset looks correct.
ZoneInfo[id="Europe/Berlin",offset=3600000,dstSavings=3600000,useDaylight=true,transitions=143,lastRule=java.util.SimpleTimeZone[id=Europe/Berlin,offset=3600000,dstSavings=3600000,useDaylight=true,startYear=0,startMode=2,startMonth=2,startDay=-1,startDayOfWeek=1,startTime=3600000,startTimeMode=2,endMode=2,endMonth=9,endDay=-1,endDayOfWeek=1,endTime=3600000,endTimeMode=2]] Instant: 2017-10-10T09:54:45.370020Z LocalDateTime: 2017-10-10T11:54:45.379338 OffsetDateTime: 2017-10-10T11:54:45.380626+02:00 ZoneInfo[id="GMT+01:00",offset=3600000,dstSavings=0,useDaylight=false,transitions=0,lastRule=null] Instant: 2017-10-10T09:54:45.394314Z LocalDateTime: 2017-10-10T10:54:45.394416 OffsetDateTime: 2021-10-10T10:54:45.394455+01:00 ZoneInfo[id="GMT+06:00",offset=21600000,dstSavings=0,useDaylight=false,transitions=0,lastRule=null] Instant: 2017-10-10T09:54:45.397258Z LocalDateTime: 2017-10-10T15:54:45.397353 OffsetDateTime: 2017-10-10T15:54:45.397406+06:00 ZoneInfo[id="GMT",offset=0,dstSavings=0,useDaylight=false,transitions=0,lastRule=null] Instant: 2017-10-10T09:54:45.399973Z LocalDateTime: 2017-10-10T09:54:45.400040 OffsetDateTime: 2017-10-10T09:54:45.400076Z
If the code base is already based on local date times and a refactoring isn’t possible the following actions could be applied to reduce the risk of moving dates and times:
- Check the current time zone in production, as all dates may require an update
- Change all dates to UTC/ GMT+0 in the DB
- Apply a fixed time zone to the JVM:
-Duser.timezone=UTC
- Apply a fixed time zone in the JDBC URL (since hibernate 5.2 a hibernate property is available)
- Verify that the date isn’t displayed to the user, or a time zone conversion is applied