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>

Solution 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.

Solution 2

Your best bet, in my view, is to tell MySQL to use GMT and handle all local time issues in your application code, not your database. The values in the database would always be GMT, full stop, which is unambiguous. As you say, with daylight savings time (summer time) adjustments, you can end up with the same value in your database for what is, to us humans, two different times.

This also makes the database portable. If you move to North America and start using MySQL set to (say) Central time, all of a sudden the values in your database seem to have moved several hours. I had that issue with a database I inherited which was using the server's local time, when I moved it from the east coast of the U.S. to the west coast, not having thought to check whether MySQL was slaved to the machine's zone...

long t = 1351382400000; // the timestamp in UTC
String insert = "INSERT INTO my_table (timestamp) VALUES (?)";
PreparedStatement stmt = db.prepareStatement(insert);
java.sql.Timestamp date = new Timestamp(t);
stmt.setTimestamp(1, date);
stmt.executeUpdate();

.....

TimeZone timezone = TimeZone.getTimeZone("MyTimeZoneId");
Calendar cal = java.util.Calendar.getInstance(timezone);
String select = "SELECT timestamp FROM my_table";
// some code omitted....
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
   java.sql.Timestamp ts = rs.getTimestamp(1);
   cal.setTimeInMillis(ts.getTime());
   System.out.println("date in db: " + cal.getTime());
}

Solution 3

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.

Solution 4

If you want to use timezone you can read column as UTC.

ZonedDateTime zdt = ZonedDateTime.of(rs.getTimestamp(1).toLocalDateTime(), ZoneOffset.UTC);

Next you can change to whatever timezone you want using:

zdt = zdt.withZoneSameInstant(ZoneId.of(
            TARGET_ZONE));

If you want only to read Date and do not care about zones at all use only:

LocalDateTime ldt = rs.getTimestamp(1).toLocalDateTime()

You will obtain LocalDateTime without timezone.

If you have to return java.util.Date use:

Date.from(ldt.atZone(ZoneOffset.UTC).toInstant());

View more solutions

Comments

[Zelldon]

Are you able to change the table layout? So you can add a column for the timezone? And save the time as timestamp?

[Jules]

No, the table is generated by third party software that I would rather not have to modify.

[Zelldon]

Hm but where should be the timezone came from if the database or the entries are not save them? You have no data to work with only a datetime. If you assume the server saves the datetime for example everytime with UTC+1(which is bst) or something else you can write code to calculate the correct timestamp.

[Jules]

The 3rd party software is storing the datetime in UTC, without reference to any server settings. Either MySQL or Java is assuming it is in local time, I want to stop it making this assumption.

[Zelldon]

Have you tried the LocalDateTime class ?docs.oracle.com/javase/8/docs/api/java/time/LocalDateTime.h‌​tml

[Jules]

I think what you're suggesting is equivalent to Ernesto's answer below; it doesn't work because I don't want to hard code timezones, and because local times have ambiguous values during DST changeover, which means the conversion that is being performed when I read from the database cannot be reversed afterwards. I need to stop it happening, rather than undoing it.

[Zelldon]

stackoverflow.com/questions/308683/… or stackoverflow.com/questions/10044807/…

[Andrew Janke]

Is this DBMail that you're using? What version?

[Meno Hochschild]

Could you kindly verify/confirm if your code expression rs.getTimestamp(1,UTCCALENDAR).getTime () has the value 1429180230000 based on the input "2015-04-16 10:30:30" (interpreted as UTC)? If so then your SQL-part is OKAY.

[Andrew Janke]

The client code you've posted looks correct, as far as it goes. What version of MySQL are you using? And what are the connection properties for your connection? Especially useLegacyDatetimeCode, serverTimeZone, and related properties. Those properties could affect date conversion behavior with respect to time zones.

[Jules]

I'm not sure I understand how this would help. The problem is that the timestamp object being returned is not accurate, because some software somewhere is assuming that it is in the system's local time. I don't want to hard-code a dependency on that local time, because it could easily be changed. I just want to load the UTC datetime value from the database and return it, which shouldn't really be this hard...

[Jules]

Also, I believe this would fail during DST changeover, as ambiguous local times would be generated and then converted potentially inaccurately into the UTC-relative Date object. The correct solution must prevent the timezone-relative conversion from happening in the first place and simply cause getTimestamp to read the underlying datetime as a UTC datetime -- as its documentation says it will.

[Ernesto Campohermoso]

Since your requirement is don't change any server setting. I assume that you want transform your UTC column in database to other timezone ("Europe/London"). Then the proposed code change the UTC time millis to the specific timezone without change the time. By example the same code can transform 10:30:24 UTC to 10:30:20 GMT-4. Just for curiosity which is the difference between UTC and "Europe/London" timezones? Maybe winter time and summer time?

[Jules]

I want to retrieve the time in a java.util.Date object without any time zone conversion. Date stores times as milliseconds since the epoch, therefore is timezone agnostic. I do not want to tie my software to running on systems with any particular timezone setting; the fact that the timezone it is running on is currently Europe/London is irrelevant in the long term and only used to illustrate the problem. You are correct that the only difference is in daylight savings.

[Jules]

Your answer is equivalent to Ernesto's (albeit without the additional dependency on Joda Time, using Java 8 features instead). It is inappropriate for at least one of the same reasons (i.e. it temporarily stores the time as a local time and then converts to UTC, which is not an acceptable solution due to the ambiguity problem of the repeated hour during DST transitions). The correct answer to this problem must prevent the incorrect timezone translation occurring at the database level, because that translation loses information.

[Jules]

Your edit doesn't address either of my two objections: it still has a hardcoded dependency on the server's timezone, and it still performs a conversion back to UTC after the incorrect translation away from it, an operation which may produce incorrect results for timestamps during the hour around DST transitions.

[Andrew Janke]

This is bad advice. SQL dates in general (and mysql in specific) are stored as year/month/.../sec fields, not millis since the epoch. Time zones are relevant for the conversion to Java dates, and only "handled" for you if you want the default behavior, which OP does not. For non-trivial applications, you do need to think about time zones, at both the server storage level and conversion steps, or you may encounter subtle bugs.

[Andrew Janke]

I think Jules is right here. The Joda-Time and other stuff is a red herring because it's "downstream" from where the problematic date conversion is happening: on the mysql server, in the JDBC driver, and in the getTimestamp() extraction method. This code is just adding an additional conversion layer after that, possibly confusing things. JDBC is not Joda-Time aware, so you can't get it in to the lower level conversions without doing your own driver hacking.

[Andrew Janke]

That said, I would still recommend switching to Joda-Time or Java 8's java.time classes like this, once the original issue is sorted out. They are so much better than java.util.Date etc. IMHO every new project should be using them by default, and just converting to java.util.Date on API boundaries that require them. :)

[Andrew Janke]

@lisiecki.m: Take another look at the dates in that error message. They are displayed in the same time zone, and they differ by an hour, not by milliseconds. This code runs in to issues because the one-argument rs.getTimestamp(1) does not "read the column as UTC"; it internally uses a time zone chosen based on Java and JDBC settings to do conversion.

[aalku]

@AndrewJanke If the server can give you the dates in any time zone why do you need to store them in a certain time zone? The only risk to avoid is thinking one date has a certain TZ being wrong. I don't see the point on working with 'string dates' with a specific time zones but to read/write from/to the user. I develop SCADA systems used and configured by users on different time zones and simply work with jave.util.Date objects or timestamps without any TZ problem.

[Andrew Janke]

You need to be concerned with what time zone you store them in on the server to avoid ambiguities in time zone conversion. For time zones which use daylight saving time, certain wall times like "2:00 AM" can occur twice on "fall back" days, and there's not enough information in the SQL date to disambiguate them. And if you don't have known, consistent, non-ambiguous time zones, server-side date arithmetic may be wrong.

[Andrew Janke]

This begs the question of how to correctly store and extract GMT dates from the server if the server's time zone is set to a different zone, which is the situation OP is asking about.

[aalku]

@AndrewJanke Well. That's broken in mysql. stackoverflow.com/questions/1646171/… I guess you need to use UTC on server, as we do.

[lisiecki.m]

@Jules mysql DATETIME does not store information about timezone so we do not lose any information. In UTC there isn't any DST transition so we cannot lose any information either. If date in DB is stored in UTC we convert it to target timezone without losing any information.

[Andrew Janke]

It's not just broken on MySQL, it's a common issue with many SQL RDBMSes, and any other data store that can store dates in naive "local" times without additional DST indicators. Which means yes, you have to think about how the server is storing the dates. Running your server in UTC is a good fix for this (I run mine in UTC too for this reason). But as OP stated, is not an option for them.

[Andrew Janke]

@liseiecki.m The DATETIME type does not itself store zone information. However, the MySQL server has a time_zone setting, and DATETIME values are implicitly treated as being in the server time zone in many cases, including, I believe, when the JDBC driver is determining what time zone they are starting in when it is converting to the client's local time and vice versa. OP's server is in a DST-observing TZ, so there's a potential for loss there.

[Toby Speight]

Whilst debugging, it's probably worthwhile running your client code in an environment with TZ=UTC to ensure that you're not assigning or converting time zones on this side of the SQL connection.