Retrieving UTC DATETIME field from MySQL in Java when server timezone is not UTC

Question

I'm trying to write code to interoperate with a third-party-developed database using Java and MySQL. This database has a field that stores a time stamp in a DATETIME field as a UTC date. The timezone for the server on which both the database and client run is set to a non-UTC zone (Europe/London), so by default the timestamp is read back incorrectly as if it were a local time. I'm trying to write code to read it back as UTC.

I have read several similar questions here, but none of them have an answer that works for me:

• MySQL - how to store time with correct timezone? (from Java)
• How to store a java.util.Date into a MySQL timestamp field in the UTC/GMT timezone?
• Date in UTC in mysql
• How do I set the time zone of MySQL?

Unfortunately, I cannot change any server settings, so I have tried using the connection's "time_zone" variable to set the database server to use UTC and the optional Calendar parameter to ResultSet.getTimestamp to retrieve the date, but this has no effect on the result. Here is my code:

private static final Calendar UTCCALENDAR = Calendar.getInstance (TimeZone.getTimeZone (ZoneOffset.UTC)); public Date getDate () {     try (Connection c = dataSource.getConnection ();          PreparedStatement s = c              .prepareStatement ("select datefield from dbmail_datefield where physmessage_id=?"))     {         fixTimeZone (c);         s.setLong (1, getPhysId ());         try (ResultSet rs = s.executeQuery ())         {             if (!rs.next ()) return null;             return new Date (rs.getTimestamp(1,UTCCALENDAR).getTime ());    // do not use SQL timestamp object, as it fucks up comparisons!         }     }     catch (SQLException e)     {         throw new MailAccessException ("Error accessing dbmail database", e);     } }  private void fixTimeZone (Connection c) {     try (Statement s = c.createStatement ())     {         s.executeUpdate ("set time_zone='+00:00'");     }     catch (SQLException e)     {         throw new MailAccessException ("Unable to set SQL connection time zone to UTC", e);     } } 

The database field I'm trying to read has a value stored in it as follows:

mysql> select * from dbmail_datefield where physmessage_id=494539; +----------------+--------+---------------------+ | physmessage_id | id     | datefield           | +----------------+--------+---------------------+ |         494539 | 494520 | 2015-04-16 10:30:30 | +----------------+--------+---------------------+ 

But unfortunately, the result comes out as BST not UTC:

java.lang.AssertionError: expected:<Thu Apr 16 11:30:30 BST 2015> but was:<Thu Apr 16 10:30:30 BST 2015> 

Answer 1

Your client getDate() code looks correct as far as it goes. I think you also need to get the MySQL Connector/J JDBC driver to treat the dates stored in the table as UTC dates, to avoid a spurious time zone conversion. This means setting the effective server time zone, in addition to the client session time zone and Calendar used for JDBC getTimestamp calls as you're doing.

Take a look at the values you got in your failed assertion, and which direction the error is in:

expected:<Thu Apr 16 11:30:30 BST 2015> but was:<Thu Apr 16 10:30:30 BST 2015> 

What you got back was 10:30 BST, which is 9:30 GMT. This is consistent with the database treating that 10:30 in the table as a BST value and spuriously converting it to GMT for you, before you parse it as a GMT date. That's the opposite direction of a GMT value being spuriously converted to BST.

This may be a JDBC-specific issue, because JDBC requires that time times be converted to the local zone. (Where the MySQL C API doesn't, probably because C's classic time types are not zone-aware the way Java's are.) And it needs to know what zone it's converting from, as well. The MySQL TIMESTAMP type is always stored as UTC. But that's not stated for the DATETIME type. I think that implies that MySQL is going to interpret DATETIME column values as being in the server's time zone. Which you mentioned as being set to BST, and that's consistent with the direction of the shift shown in your assertion error message.

The time_zone session variable you set is telling the MySQL server what your client machine's time zone is, but it doesn't affect what the server thinks its own time zone is. That can be overridden with the serverTimezone JDBC connection property. On your connection, set the serverTimezone to UTC, and make sure useLegacyDatetimeCode is off. (And look through the other zone-related properties if that doesn't work.) See if that gets your dates to come through as UTC with the same calendar field values as in the database.

Be aware that this is going to change the interpretation of other DATETIME values in your database: they're all going to look like UTC dates now (in the context of your JDBC connection). Whether that's correct is going to depend on how they were populated initially. While your client code will have the behavior you want, I don't know if this system as a whole can be made to behave fully consistently without setting the server's time zone to UTC at the server level. Basically, if it doesn't have its zone set to UTC, it's not fully configured for the behavior you want, and you're kludging around it.

Answer 2

Maybe you can use JodaTime as follows;

private static final Calendar UTCCALENDAR = Calendar.getInstance (TimeZone.getTimeZone (ZoneOffset    .UTC)); public Date getDate () {     try (Connection c = dataSource.getConnection ();          PreparedStatement s = c              .prepareStatement ("select datefield from dbmail_datefield where physmessage_id=?"))     {         s.setLong (1, getPhysId ());         try (ResultSet rs = s.executeQuery ())         {             if (!rs.next ()) return null;             DateTime dt = new LocalDateTime(rs.getTimestamp(1,UTCCALENDAR).getTime ()).toDateTime(DateTimeZone.forID("Europe/London"));                return dt.toDate();               }     }     catch (SQLException e)     {         throw new MailAccessException ("Error accessing dbmail database", e);     } } 

EDIT:

java.util.Date is not TimeZone agnostic. The method toDateTime takes care of TimeZone and DST so you don't care about it

The following code:

public static void main(String[] args) {     // 29/March/2015 1:05 UTC     DateTime now = new DateTime(2015, 3,29,1,5,DateTimeZone.UTC);     // Pre DST 29/March/2015 0:30 UTC     DateTime preDst = new DateTime(2015, 3,29,0,30,DateTimeZone.UTC);     System.out.println("1:05 UTC:"+now);     System.out.println("0:30 UTC:"+preDst);     DateTimeZone europeDTZ = DateTimeZone.forID("Europe/London");     DateTime europeLondon = now.toDateTime(europeDTZ);     System.out.println("1:05 UTC as Europe/London:"+europeLondon);     DateTime europeLondonPreDst = preDst.toDateTime(europeDTZ);     System.out.println("0:30 UTC as Europe/London:"+europeLondonPreDst); } 

Will print:

1:05 UTC:2015-03-29T01:05:00.000Z 0:30 UTC:2015-03-29T00:30:00.000Z 1:05 UTC as Europe/London:2015-03-29T02:05:00.000+01:00 0:30 UTC as Europe/London:2015-03-29T00:30:00.000Z 

If you can see JodaTime takes care of DST.