JDBC/MySQL: Save timestamp always using UTC

问题:

I would like to save a timestamp to the database without being converted to the local timezone by the jdbc driver. Currently only MySQL and PostgreSQL are important for me but i would appreciate if there is a database independent solution.

Example:

// i want that to be saved as 1970-01-01 00:00:00
TimeStamp ts = new java.sql.Timestamp(0);

// gets transformed to local time by jdbc driver (in my case to 1970-01-01 01:00:00)
st.setTimestamp(0, new java.sql.Timestamp(0));

// only works using postgres (mysql connector seems to ignore the calendar)
// postgres => 1970-01-01 00:00:00
// mysql => 1970-01-01 01:00:0
Calendar calutc = Calendar.getInstance(TimeZone.getTimeZone("UTC"));
st.setTimestamp(0, new java.sql.Timestamp(0), utccal);

I already tried to calculate a timestamp value that get transformed to the correct value (for example -3600000 => 1970-01-01 00:00:00 in my time zone) but this doesn't work on postgres on dates near the day light saving time changes.

回答1:

I found the solution. MySQL had a bug in their JDBC connector ignoring the provided Calendar object to setTimestamp/getTimestamp. They fixed the bug in version 5.1.5 of the connector but the old (incorrect) behaviour is still the default behaviour. To use the correct code you have to pass the parameter "useLegacyDatetimeCode=false" to the connector url.

Further information: http://bugs.mysql.com/bug.php?id=15604

回答2:

Date and Timestamp in Java are timezone-agnostic. The new Timestamp(0) indeed corresponds to the the 1970-01-01 00:00:00 in UTC. You have to use SimpleDateFormat and set the desired timezone on it to visually inspect your dates. Subtracting a long constant from a timestamp to make it look "right" when printed out using System.out.println is very wrong.

When using a database, you have a choice of data types for date/time/timestamp. Some of them support time zone information and some - don't. If you choose to use time zone handling in your database, you have to learn it in details. If you want to bypass it, you have an option to store dates/times/timestamps in the database as strings and do all formatting in your Java code.