2

In Apache OAK, we use SQL's CURRENT_TIMESTAMP to ask the database for it's idea of the current time (not in order to store it, but to verify that all Oak instances talking to the database have roughly the same system time).

The value is received using a JDBC ResultSet and getTimestamp().getTime(), which is supposed to return a value suitable for comparison with System.currentTimeMillis().

This appeared to work, until somebody tried to connect to a IBM DB2 instance running in a different timezone, in which case the returned timestamp was off by either the TZ difference (or potentially a DST offset constant) - details at https://issues.apache.org/jira/browse/OAK-3645.

So the questions are:

  1. Is this a known issue with DB2 or its JDBC driver?
  2. Will this happen for other databases as well?
  3. Is there an alternative that will work both reliably and portably?
Julian Reschke
  • 40,156
  • 8
  • 95
  • 98
  • 1
    This is a Java/JDBC problem. A `java.sql.Timestamp` which is derived from `java.util.Date` has no notion of a time zone. There is a conversion going on, on two levels: the driver **and** the Java runtime. The problem isn't unique to DB2, other DBMS (and drivers) "suffer" from the same problem. The only "reliable" way I can think of is to let the _server_ do the conversion and return the timestamp as a character string (by using formatting functions in the SQL query). Then process that on the client side. Thus you at least eliminate the driver as a potential problem. –  Nov 17 '15 at 12:41
  • ANSI/ISO SQL: CURRENT_TIMESTAMP includes timezone. LOCALTIMESTAMP is equivalent to CAST (CURRENT_TIMESTAMP AS TIMESTAMP WITHOUT TIME ZONE). – jarlh Nov 17 '15 at 13:03
  • See [my answer to "Is java.sql.Timestamp timezone specific?"](http://stackoverflow.com/questions/14070572/is-java-sql-timestamp-timezone-specific/14070771#14070771) – Mark Rotteveel Nov 17 '15 at 16:24
  • @jarlh That assumes the database server supports timezones, and that it fully and correctly implements the standard. – Mark Rotteveel Nov 18 '15 at 08:59
  • @MarkRotteveel, I know. But isn't db2 in general one of the more ANSI SQL compatible databases? – jarlh Nov 18 '15 at 09:08

2 Answers2

3

The problem is that the JDBC specification requires that drivers interpret TIME (or TIMESTAMP) without timezone information as if the value is in the local timezone of the JVM. See also Is java.sql.Timestamp timezone specific?

As an example. Your database server is in UTC, and the JVM in CET (UTC+1). The current time in UTC is 10:00, this means that if you ask for the current time, it is "10:00" on the server. The TIME datatype has no timezone, so when the JVM queries this, it has to convert this to mean "10:00" in its local timezone. So the result is 10:00 CET, (which is 9:00 UTC), in other words: a difference of one hour.

Based on the provided information it sounds like the DB2 driver is doing what it should.

Unfortunately not all JDBC drivers do this right, and even worse some drivers can be configured to behave differently.

Community
  • 1
  • 1
Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
0

In DB2 the value of the CURRENT_TIMESTAMP always represents the database server operating system time. You can normalize the value to UTC by using the CURRENT_TIMEZONE value; subtracting it from CURRENT_TIMESTAMP will yield UTC timestamp:

SELECT CURRENT_TIMESTAMP - CURRENT_TIMEZONE AS timestamp_UTC FROM sysibm.sysdummy1
mustaccio
  • 18,234
  • 16
  • 48
  • 57