How to store a java.util.Date into a MySQL timestamp field in the UTC/GMT timezone?

Question

I used a new Date() object to fill a field in a MySQL DB, but the actual value stored in that field is in my local timezone.

How can I configure MySQL to store it in the UTC/GMT timezone?

I think, configuring the connection string will help but I don't know how. There are many properties in the connection string like useTimezone, serverTimzone, useGmtMillisForDatetimes, useLegacyDatetimeCode, ...

Answer 1

Well, if we're talking about using PreparedStatements, there's a form of setDate where you can pass in a Calendar set to a particular time zone.

For instance, if you have a PreparedStatement named stmt, a Date named date, and assuming the date is the second parameter:

stmt.setDate(2, date, Calendar.getInstance(TimeZone.getTimeZone("GMT")));

The best part is, even if GMT is an invalid time zone name, it still returns the GMT time zone because of the default behavior of getTimeZone.

Answer 2

The short answer is:

• add "default-time-zone=utc" to my.cnf
• in your code, always "think" in UTC, except when displaying dates for your users

• when getting/setting dates or timestamps with JDBC, always use the Calendar parameter, set to UTC:

  resultset.getTimestamp("my_date", Calendar.getInstance(TimeZone.getTimeZone("UTC")));

• either synchronize your servers with NTP, or rely only on the database server to tell you what time it is.

---

The long answer is this:

When dealing with dates and timezones in any database and with any client code, I usually recommend the following policy:

1. Configure your database to use UTC timezone, instead of using the server's local timezone (unless it is UTC of course).

   • How to do so depends on your database server. Instructions for MySQL can be found here: http://dev.mysql.com/doc/refman/5.0/en/time-zone-support.html. Basically you need to write this in my.cnf: default-time-zone=utc

   • This way you can host your database servers anywhere, change your hosting location easily, and more generally manipulate dates on your servers without any ambiguity.

   • If you really prefer to use a local timezone, I recommend at least turning off Daylight Saving Time, because having ambiguous dates in your database can be a real nightmare.
     • For example, if you are building a telephony service and you are using Daylight Saving Time on your database server then you are asking for trouble: there will be no way to tell whether a customer who called from "2008-10-26 02:30:00" to "2008-10-26 02:35:00" actually called for 5 minutes or for 1 hour and 5 minutes (supposing Daylight Saving occurred on Oct. 26th at 3am)!

2. Inside your application code, always use UTC dates, except when displaying dates to your users.

   • In Java, when reading from the database, always use:

   Timestamp myDate = resultSet.getTimestamp("my_date", Calendar.getInstance(TimeZone.getTimeZone("UTC")));

   • If you do not do this, the timestamp will be assumed to be in your local TimeZone, instead of UTC.

3. Synchronize your servers or only rely on the database server's time

   • If you have your Web server on one server (or more) and your database server on some other server, then I strongly recommend you synchronize their clocks with NTP.

   • OR, only rely on one server to tell you what time it is. Usually, the database server is the best one to ask for time. In other words, avoid code such as this:

   preparedStatement = connection.prepareStatement("UPDATE my_table SET my_time = ? WHERE [...]");
   java.util.Date now = new java.util.Date(); // local time! :-(
   preparedStatement.setTimestamp(1, new Timestamp(now.getTime()));
   int result = preparedStatement.execute();

   • Instead, rely on the database server's time:

   preparedStatement = connection.prepareStatement("UPDATE my_table SET my_time = NOW() WHERE [...]");
   int result = preparedStatement.execute();

Hope this helps! :-)

Answer 3

A java Date is timezone agnostic. It ALWAYS represents a date in GMD(UTC) in milliseconds from the Epoch.

The ONLY time that a timezone is relevant is when you are emitting a date as a string or parsing a data string into a date object.