Still confused by Java Timestamps etc with MySQL

Question

After searching and reading about how to handle date and time to/from Java and MySQL I am still confused.

Lets say I create a java.util.Date object. That object holds time in UTC. Any formatting or parsing into other time zones can the be made with e.g. java.text.SimpleDateFormat.

Now I want to store my date object into the MySQL database in UTC. But when I use the setTimestamp() method in java.sql.PreparedStatement I get a bit confused. Here follows some sample code where I test both MySQL DATETIME and TIMESTAMP in my table. I also insert the dates with both the setString() and setTimestamp() methods.

java.sql.Connection conn = java.sql.DriverManager.getConnection("jdbc:mysql://localhost/test","user","password");
java.sql.Statement st = conn.createStatement();

String q = "DROP TABLE IF EXISTS tmp";
st.execute(q);
q = "CREATE TABLE tmp (dt_string TEXT, dt DATETIME, ts TIMESTAMP)";
st.execute(q);

java.sql.PreparedStatement pst = conn.prepareStatement("INSERT INTO tmp SET dt_string=?, dt=?, ts=?");

java.text.SimpleDateFormat utc = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
utc.setTimeZone(java.util.TimeZone.getTimeZone("UTC"));

java.util.TimeZone.setDefault(java.util.TimeZone.getTimeZone("EST"));

System.out.println("Default time zone: " + java.util.TimeZone.getDefault().getID());
java.util.Date d = new java.util.Date();
System.out.println("A date: " + d);
java.sql.Timestamp t = new java.sql.Timestamp( d.getTime() );
System.out.println("The timestamp: " + t);


pst.setString(1, utc.format(d) );
pst.setString(2, utc.format(d) );
pst.setString(3, utc.format(t) );
pst.execute();

pst.setTimestamp(2, t);
pst.setTimestamp(3, t);
pst.execute();

System.out.println("Use calendar: " + utc.getCalendar().getTimeZone() );
pst.setTimestamp(2, t, utc.getCalendar());
pst.setTimestamp(3, t, utc.getCalendar());
pst.execute();

conn.close();

When I run the above I get the following output which is as expected.

Default time zone: EST
A date: Thu Mar 22 08:49:51 EST 2012
The timestamp: 2012-03-22 08:49:51.784
Use calendar: sun.util.calendar.ZoneInfo[id="UTC",offset=0,dstSavings=0,useDaylight=false,transitions=0,lastRule=null]

But when I inspect the table in the database using the MySQL command line tool I get:

mysql> select * from tmp;
+---------------------+---------------------+---------------------+
| dt_string           | dt                  | ts                  |
+---------------------+---------------------+---------------------+
| 2012-03-22 13:49:51 | 2012-03-22 13:49:51 | 2012-03-22 13:49:51 |
| 2012-03-22 13:49:51 | 2012-03-22 08:49:51 | 2012-03-22 08:49:51 |
| 2012-03-22 13:49:51 | 2012-03-22 08:49:51 | 2012-03-22 08:49:51 |
+---------------------+---------------------+---------------------+
3 rows in set (0.00 sec)

The first column is just a TEXT type where I store the UTC formatted date.

In the first row the I stored the dates using the setString() method.

In the second row I stored the date using the setTimestamp(i,t) method. I guess that JDBC automatically converts the date using the default time zone (which I set to EST) before it stores it. But shouldn't TIMESTAMP always automatically be stored in UTC. The MySQL documentation says MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (Issue 1).

Finally, for the third row I used the pst.setTimestamp(2, t, utc.getCalendar()); to store the date with hope that the driver should use the UTC time zone. But apparently not (Issue 2).

I can easily fix the problem to store dates in UTC by setting the default time zone to UTC. But still, I would like to understand what is going on for the two issues above.

Answer 1

Finally, I am understanding something, but using PostgreSQL instead. I basically repeated the code above using

Connection conn = DriverManager.getConnection("jdbc:postgresql://localhost");

st.execute("CREATE TABLE tmp (ts_string TEXT, ts TIMESTAMP WITH TIME ZONE)");
//st.execute("CREATE TABLE tmp (ts_string TEXT, ts TIMESTAMP WITHOUT TIME ZONE)");

and writing the dates as

pst.setString(1, utc.format(d));
pst.setTimestamp(2, t);
pst.execute();

pst.setTimestamp(2, t, utc.getCalendar());
pst.execute();

When TIMESTAMP WITH TIMEZONE is used I get the following output from psql

postgres=# select * from tmp;
        ts_string        |             ts             
-------------------------+----------------------------
 2012-03-23 12:48:28.057 | 2012-03-23 13:48:28.057+01
 2012-03-23 12:48:28.057 | 2012-03-23 13:48:28.057+01
(2 rows)

because it's keeping track of the system (server) time zone, which is CET (+01). The dates are correct although displayed in CET.

If I instead use TIMESTAMP WITHOUT TIME ZONE I get

postgres=# select * from tmp;
        ts_string        |           ts           
-------------------------+------------------------
 2012-03-23 12:49:04.120 | 2012-03-23 07:49:04.12
 2012-03-23 12:49:04.120 | 2012-03-23 12:49:04.12
(2 rows)

and in the first case it uses the default time zone (which I set to EST), and the date is naturally "wrong", just as in the MySQL case. However, in the second case it uses UTC because I passed that as an argument to the setTimestamp() method, and this is what I tried to do in MySQL. To me, it seems that the MySQL java driver just ignores the Calendar argument in setTimestamp(). Maybe I missed something.

Answer 2

MySql datetime fields are awesome once you wrap your head around them.

the "SimpleDateFormat" you are using doesn't have a time zone attached to it, so your database assumes that you are entering a date in your server's timezone. If you are in a -0500 time zone, it adds 5 hours to it to convert it to UTC, and then when you fetch it, it subtracts the 5 hours to convert it back to your local time.

When you converted your timestamp to utc before you inserted it, you still didn't tack a timezone onto the string. Your database performed the same operation on it. 5 hours were added to 2012-03-22 13:49:51, so your db stored 2012-03-22 18:49:51 (still assuming -0500).

The really fun part of all this is that you can set the timezone on a database connection, and all of the datetimes you fetch or insert will shift along with your connection!