Geek Answers Handbook

JDBC / MySQL: saving timestamp always using UTC

I would like to save the timestamp in the database without converting to the local timezone using the jdbc driver. Currently, only MySQL and PostgreSQL are important to me, but I would appreciate it if there was 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 have already tried to calculate the timestamp value, which will be converted to the correct value (for example, -3600000 => 1970-01-01 00:00:00 in my time zone), but this does not work on postgres in the dates the time saving changes during the day .

+3
source share
2 answers

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

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

+11
source

The date and timestamp in Java are time zone independent. The new timestamp (0) really corresponds to 1970-01-01 00:00:00 in UTC. You must use SimpleDateFormat and set the desired time zone on it to visually check your dates. Subtracting a long constant from a timestamp so that it looks “right” when a print using System.out.println was very wrong.

When using a database, you have a choice of data types for the date / time / timestamp. Some of them support time zone information, and some do not. If you decide to use time zone processing in your database, you should study it in detail. If you want to get around it, you have the option of storing dates / time / timestamps in the database as strings and doing all the formatting in your Java code.

0
source

More articles:


All Articles