Java MySQL Timestamp time zone problems

Question

I have a java.util.Date object, and I need to insert it into a datetime field in MySQL in UTC format.

java.util.Date date = myDateFromSomewhereElse;
PreparedStatement prep = con.prepareStatement(
    "INSERT INTO table (t1, t2) VALUES (?,?)");

java.sql.Timestamp t = new Timestamp(date.getTime());
prep.setTimestamp(1, t, Calendar.getInstance(TimeZone.getTimeZone("PST"));
prep.setTimestamp(2, t, Calendar.getInstance(TimeZone.getTimeZone("UTC"));
System.out.println(prep.toString());

Which gives me the prepared SQL statement string:

INSERT INTO table (t1, t2) VALUES ('2012-05-09 11:37:08','2012-05-09 11:37:08');

The timestamp returned is the same timestamp regardless of the timezone I specify. It's ignoring the Calendar object with timezone that I specify. What is going on and what am I doing wrong?

Solution 1

TimeZones are just different ways to view a date (which is a fixed point in time). I wrote a little example here (pay close attention to the assert):

// timezone independent date (usually interpreted by the timezone of 
// the default locale of the user machine)
Date now = new Date();

// now lets get explicit with how we wish to interpret the date
Calendar london =  Calendar.getInstance(TimeZone.getTimeZone("Europe/London"));
Calendar paris = Calendar.getInstance(TimeZone.getTimeZone("Europe/Paris"));

// now set the same date on two different calendar instance
london.setTime(now);
paris.setTime(now);

// the time is the same
assert london.getTimeInMillis() == paris.getTimeInMillis();

// London is interpreted one hour earlier than Paris (as of post date of 9th May 2012)
String londonTime = london.get(Calendar.HOUR) + ":" + london.get(Calendar.MINUTE);
String londonTZ = london.getTimeZone().getDisplayName(london.getTimeZone().inDaylightTime(london.getTime()), TimeZone.SHORT);
System.out.println(londonTime + " " + londonTZ);

// Paris is interpreted one hour later than Paris (as of post date of 9th May 2012)
String parisTime = paris.get(Calendar.HOUR) + ":" + paris.get(Calendar.MINUTE);
String parisTZ = paris.getTimeZone().getDisplayName(paris.getTimeZone().inDaylightTime(paris.getTime()), TimeZone.SHORT);
System.out.println(parisTime + " " + parisTZ);

The output to this snippet is (the result will be different depending on execution date/time):

8:18 BST
9:18 CEST

Your snippet in the question is simply not doing anything with regard to the date being stored. Usually databases are configured for a native TimeZone. I advise storing an extra field representing the TimeZone to be used when interpreting the date.

It is not (generally) a good idea to modify dates (which are essentially just milliseconds before/after a fixed point in time) as this would be a lossy modification that would be interpreted differently at different points in the year (due to daylight savings time).

Or this : http://puretech.paawak.com/2010/11/02/how-to-handle-oracle-timestamp-with-timezone-from-java/

Solution 2

Jordan, actually you had the right idea. The problem is there's a bug in MySQL JDBC driver and the Calendar argument is completely ignored by default. Look at the source code for PreparedStatement to really see what's going on.

Notice it format's the Timestamp using the JVM's time zone. This will only work if your JVM is using UTC time zone. The Calendar object is completely ignored.

this.tsdf = new SimpleDateFormat("''yyyy-MM-dd HH:mm:ss''", Locale.US);
timestampString = this.tsdf.format(x);

In order for MySQL to use the Calendar argument, you have to disable the legacy date/time code with the following connection option:

useLegacyDatetimeCode=false

So you might use it when connecting to the database like this:

String url = "jdbc:mysql://localhost/tz?useLegacyDatetimeCode=false"

If you disable the legacy datetime code using the above line, then it WILL render your Timestamp in the target Calendar's time zone:

if (targetCalendar != null) {
    targetCalendar.setTime(x);
    this.tsdf.setTimeZone(targetCalendar.getTimeZone());

     timestampString = this.tsdf.format(x);
} else {
    this.tsdf.setTimeZone(this.connection.getServerTimezoneTZ());
    timestampString = this.tsdf.format(x);
}

It's pretty easy to see what's going on here. If you pass in a Calendar object, it will use this when formatting the data. Otherwise, it will use the database's time zone to format the data. Strangely, if you pass in a Calendar, it will also set the time to the given Timestamp value (which seems to be pointless).

Solution 3

Check this link for explanation for MySQL (and you shouldn't try to apply advices about Oracle to MySQL).

The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.

MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.) By default, the current time zone for each connection is the server's time.