Still confusing 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.

Suppose I create a java.util.Date object. This object stores time in UTC. Any formatting or parsing in other time zones can be performed, for example. java.text.SimpleDateFormat .

Now I want to save my date object in a MySQL database in UTC. But when I use the setTimestamp() method in setTimestamp() , I'm a little confused. Below is an example code example in which I test MySQL DATETIME and TIMESTAMP in my table. I also insert dates using 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 above, I get the following result, 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 check 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 date in UTC.

On the first line, I saved the dates using the setString() method.

In the second line, I saved the date using the setTimestamp(i,t) method. I assume JDBC will automatically convert the date using the default time zone (which I set to EST) before it saves it. But you should not always save TIMESTAMP in UTC. The MySQL documentation says that 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 line, I used pst.setTimestamp(2, t, utc.getCalendar()); to store the date with the hope that the driver should use the UTC time zone. But apparently not (problem 2).

I can easily fix the problem of storing dates in UTC by setting the default time zone in UTC. Nevertheless, I would like to understand what is happening on the two above issues.

Answer 1

Finally, I understand something, but use 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 recording dates as

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

When using TIMESTAMP WITH TIMEZONE , 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) 

as it keeps track of the time zone of the system (server), which is CET (+01). The dates are correct, although they appear in CET.

If I use TIMESTAMP WITHOUT TIME ZONE instead, 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 “incorrect”, as in the case of MySQL. However, in the second case, it uses UTC because I passed this as an argument to the setTimestamp() method, and this is what I tried to do in MySQL. It seems to me that the MySQL Java driver simply ignores the Calendar argument in setTimestamp() . Maybe I missed something.

Answer 2

MySql datetime fields are awesome when you hug them around.

The "SimpleDateFormat" that you use does not have a time zone attached to it, so your database assumes that you enter a date in the time zone of your server. If you are in the -0500 time zone, it adds 5 hours to it to convert it to UTC, and then when you extract it, it subtracts 5 hours to convert it back to local time.

When you set the timestamp to utc before inserting it, you still did not bind the time zone to the string. Your database performed the same operation on it. 5 hours were added on 2012-03-22 13:49:51, so your db was saved on 2012-03-22 18:49:51 (-0500 is still assumed).

The most interesting thing is that you can set the time zone for connecting to the database, and all the time you select or insert will move with your connection!