Dates, DateTime, Timezone and the DB

TL;DR Summary

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

Links

• https://docs.oracle.com/javase/9/troubleshoot/time-zone-settings-jre.htm