What’s Even Harder Than Dates and Timezones? Dates and Timezones in SQL / JDBC!

Posted on by lukaseder
(Notice, this post has been written a while ago. jOOQ now supports JSR 310 data types) There was an interesting discussion recently on the jOOQ mailing list about jOOQ’s current lack of out-of-the-box support for TIMESTAMP WITH TIME ZONE data types. No one said that date, time and timezones are easy! There’s an amusing piece here, which I recommend reading: Falsehoods programmers believe about time And when that’s not enough, read also: More falsehoods programmers believe about time I personally like the bit about programmers erroneously believing that “Unix time is the number of seconds since Jan 1st 1970.”… unix time doesn’t have a way to represent leap seconds ;)

Back to JDBC

Here’s an interesting Stack Overflow answer by Mark Rotteveel, the Jaybird developer (Firebird JDBC driver): Is java.sql.Timestamp timezone specific? Mark’s explanation can be observed as follows (I’m using PostgreSQL here): 

Connection c = getConnection();
Calendar utc = Calendar.getInstance(
    TimeZone.getTimeZone("UTC"));

try (PreparedStatement ps = c.prepareStatement(
    "select"
  + "  ?::timestamp,"
  + "  ?::timestamp,"
  + "  ?::timestamp with time zone,"
  + "  ?::timestamp with time zone"
)) {

    ps.setTimestamp(1, new Timestamp(0));
    ps.setTimestamp(2, new Timestamp(0), utc);
    ps.setTimestamp(3, new Timestamp(0));
    ps.setTimestamp(4, new Timestamp(0), utc);

    try (ResultSet rs = ps.executeQuery()) {
        rs.next();

        System.out.println(rs.getTimestamp(1) 
                 + " / " + rs.getTimestamp(1).getTime());
        System.out.println(rs.getTimestamp(2, utc)
                 + " / " + rs.getTimestamp(2, utc).getTime());
        System.out.println(rs.getTimestamp(3) 
                 + " / " + rs.getTimestamp(3).getTime());
        System.out.println(rs.getTimestamp(4, utc)
                 + " / " + rs.getTimestamp(4, utc).getTime());
    }
}
The above program uses all permutations of using timezones and not using timezones in Java and in the DB, and the output is always the same: 
1970-01-01 01:00:00.0 / 0
1970-01-01 01:00:00.0 / 0
1970-01-01 01:00:00.0 / 0
1970-01-01 01:00:00.0 / 0
As you can see, in each case, the UTC timestamp 0 was correctly stored and retrieved from the database. My own locale is Switzerland, thus CET / CEST, which was UTC+1 at Epoch, which is what is getting output on Timestamp.toString(). Things get interesting when you use timestamp literals, both in SQL and/or in Java. If you replace the bind variables as such: 

Timestamp almostEpoch = Timestamp.valueOf("1970-01-01 00:00:00");

ps.setTimestamp(1, almostEpoch);
ps.setTimestamp(2, almostEpoch, utc);
ps.setTimestamp(3, almostEpoch);
ps.setTimestamp(4, almostEpoch, utc);
This is what I’m getting on my machine, again in CET / CEST 
1970-01-01 00:00:00.0 / -3600000
1970-01-01 00:00:00.0 / -3600000
1970-01-01 00:00:00.0 / -3600000
1970-01-01 00:00:00.0 / -3600000
I.e. not Epoch, but the timestamp literal that I sent to the server in the first place. Observe that the four combinations of binding / fetching still always produce the same timestamp. Let’s see what happens if the session writing to the database uses a different timezone (let’s assume you’re in PST) than the session fetching from the database (I’m using again CET or UTC). I’m running this program: 

Calendar utc = Calendar.getInstance(
    TimeZone.getTimeZone("UTC"));

Calendar pst = Calendar.getInstance(
    TimeZone.getTimeZone("PST"));

try (PreparedStatement ps = c.prepareStatement(
    "select"
  + "  ?::timestamp,"
  + "  ?::timestamp,"
  + "  ?::timestamp with time zone,"
  + "  ?::timestamp with time zone"
)) {

    ps.setTimestamp(1, new Timestamp(0), pst);
    ps.setTimestamp(2, new Timestamp(0), pst);
    ps.setTimestamp(3, new Timestamp(0), pst);
    ps.setTimestamp(4, new Timestamp(0), pst);

    try (ResultSet rs = ps.executeQuery()) {
        rs.next();

        System.out.println(rs.getTimestamp(1)
                 + " / " + rs.getTimestamp(1).getTime());
        System.out.println(rs.getTimestamp(2, utc)
                 + " / " + rs.getTimestamp(2, utc).getTime());
        System.out.println(rs.getTimestamp(3)
                 + " / " + rs.getTimestamp(3).getTime());
        System.out.println(rs.getTimestamp(4, utc)
                 + " / " + rs.getTimestamp(4, utc).getTime());
    }
}
It yields this output: 
1969-12-31 16:00:00.0 / -32400000
1969-12-31 17:00:00.0 / -28800000
1970-01-01 01:00:00.0 / 0
1970-01-01 01:00:00.0 / 0
The first timestamp was Epoch stored as PST (16:00), then the timezone information was removed by the database, which turned Epoch into the local time you had at Epoch (-28800 seconds / -8h), and that’s the information that is really stored. Now, when I’m fetching this time from my own timezone CET, I will still want to get the local time (16:00). But in my timezone, this is no longer -28800 seconds, but -32400 seconds (-9h). Quirky enough? Things go the other way round when I’m fetching the stored local time (16:00), but I’m forcing the fetch to happen in UTC, which will produce the timestamp that you’ve stored, originally in PST (-28800 seconds). But when printing this timestamp (-28800 seconds) in my timezone CET, this will now be 17:00. When we use the TIMESTAMP WITH TIME ZONE data type in the database, the timezone is maintained (PST), and when I fetch the Timestamp value, no matter if using CET or UTC, I will still get Epoch, which was safely stored to the database, printed out as 01:00 in CET. Whew.

TL;DR:

(Notice, this post has been written a while ago. jOOQ now supports JSR 310 data types) When using jOOQ’s, if the correct UTC timestamp matters to you, use TIMESTAMP WITH TIMEZONE, but you’ll have to implement your own data type Binding, because jOOQ currently doesn’t support that data type. Once you use your own data type Binding, you can also use Java 8’s time API, which better represents these different types than java.sql.Timestamp + the ugly Calendar. If the local time matters to you, or if you’re not operating across time zones, you’re fine using TIMESTAMP and jOOQ’s Field<Timestamp>. Lucky you, if you’re like me, operating in a very small country with a single time zone where most local software just doesn’t run into this issue.

Published by lukaseder

I made jOOQ

10 thoughts on “What’s Even Harder Than Dates and Timezones? Dates and Timezones in SQL / JDBC!”

  1. I must confess that the more I know about all the stuff, the more I hate it. I guess, what’s needed most is to store an instant (in the Joda Time sense) and after having read your article, I still don’t know how to do it (except for using milliseconds since the Epoch). Not your fault, as I wasn’t reading carefully, but such things such be obvious from the Javadoc alone.

    Once I thought, `java.lang.Date` was the most terrible class ever, but the `java.sql` package wins hands down.

    Reply

    1. I must confess that the more I know about all the stuff, the more I hate it.

      Well, it’s Java, after all, right? :)

      In the end, it’s not really all that difficult. The only problem is the fact that JDBC based their useful Date, Time, and Timestamp types on java.util.Date, rather than creating simple, independent classes (and adding TimeWithTimeZone as well as TimestampWithTimeZone).

      And the best thing is, JDBC 4.2 doesn’t really add formal API support for the java.time package. Probably due to lack of time and budget. But hey, it’s a standard right? Thanks JCP ;)

      </sarcasm>

      On a more serious note: That’s why you need jOOQ. We’re cleaning up with all these atrocities.

      Reply

      1. Not sure we talk about the same thing. JDBC 4.2 does add support for java.time but only through `#getObject` / `#setObject`. It’s debatable whether you want to call that “formal API”.

        Reply

        1. OK, to be fair, formal “support” is added by loosely specifying things in the JDBC 4.2 specs. Which isn’t exactly the same as adding new (default) methods to ResultSet, PreparedStatement, CallableStatement, SQLInput, and SQLOutput, which would have been much more desireable in my opinion, specifically because getObject() implementations cannot change behaviour backwards-compatibly.

          Reply

  2. Some vendors (like Oracle) have extensions like TIMESTAMPTZ that allow you to fix this. You can even write you own JPA AttributeConverters for this.

    Reply

    1. You’re right but in times of Java 8, it doesn’t feel too good to resort to a data type of the quality of TIMESTAMPTZ:

      • non-open source
      • low level
      • depending on a JDBC Connection for data type transformation

      Besides, have you tried the JPA AttributeConverter (e.g. with Hibernate?) There seems to be a bug that prevents this usage:

      https://hibernate.atlassian.net/browse/HHH-9553

      Reply

      1. I’m not saying it’s perfect, I’m saying it’s possible.
        – TIMESTAMPTZ is just a byte[13], so there’s no real “source”.
        – Yes, it’s low level but you can covert it to a high-level / Java 8 date object. Word on the street is the Oracle JDBC driver will support JDBC 4.2 / Java 8 dates not until Oracle 12.2c ships.
        – I haven’t found any need to depend on the JDBC connection for data type information.

        I wrote my own attribute converter. However access to TIMESTAMPTZ from JPA in my experience only works with EclipseLink and does not work with Hibernate or OpenJPA. It seems intentional they don’t support it.

        https://github.com/marschall/threeten-jpa/blob/master/threeten-jpa-oracle-eclipselink/src/main/java/com/github/marschall/threeten/jpa/oracle/OracleTimeConverter.java

        Reply

        1. Good to know about 12.2c upgrades, and thanks for the link.

          Curious, what itentions are you implying for the lack of support for accessing TIMESTAMPTZ in Hibernate / OpenJPA?

          Reply

          1. Both Hibernate an OpenJPA have feature requests in their issue trackers for supporting TIMESTAMPTZ. OpenJPA has marked the issue as Won’t fix OPENJPA-1480. The Hibernate issue is open since 7 years HHH-3193 but I’m not sure that simply registering would bring support for TIMESTAMPTZ or additional work would be required.

            At the same time it is not clear whether JPA_SPEC-63 is actually going to happen. From the outside there seems to be very little progress happening.

            It looks as if Hibernate 5 is going to support Java 8 dates (HHH-8844) but it’s unclear to me whether that will rely on JDBC 4.2 support (and therefore not work with Oracle for a long time) or they have special code for driver extensions (eg. TIMESTAMPTZ).

            Reply

Leave a Reply