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?

Accepted Answer

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/

Java MySQL Timestamp time zone problems

Solution

Problem