JDBC/MySQL: Save timestamp always using UTC

Question

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.

Werzi2001 · Accepted Answer · 2013-03-05 15:22:05Z

13

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

answered Mar 5, 2013 at 15:22

Werzi2001

2,1651 gold badge20 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

snowindy Over a year ago

I used '&serverTimezone=UTC&useLegacyDatetimeCode=false' with success, without serverTimezone jdbc driver complained about incorrect timezone.

snowindy Over a year ago

Full URL, for reference: jdbc:mysql://localhost:3306/pm_func?characterEncoding=UTF-8&useEncoding=true&autoReconnect=true&serverTimezone=UTC&useLegacyDatetimeCode=false

Olaf · Accepted Answer · 2013-03-05 15:41:13Z

1

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.

edited Mar 5, 2013 at 15:41

answered Mar 4, 2013 at 20:37

Olaf

6,2991 gold badge21 silver badges38 bronze badges

5 Comments

Werzi2001 Over a year ago

As far as i know jdbc only supports setTimestamp for date and time values (setDate is only date) which does the time zone transformation. I think the correct solution is the setTimestamp with Calendar attribute. I found a MySQL Connector attribute "useLegacyDatetimeCode=false" which seems to enalbe jdbc compliant behaviour for MySQL (@MySQL: wtf?)

Olaf Over a year ago

PostgreSQL has several date/time types: timestamp with time zone, timestamp without time zone, date, time with time zone, time without time zone. I bet MySQL has a similar variety of types. Unfortunately, I believe those types are not standardized, so different databases have similar but not identical sets of date/time types.

Werzi2001 Over a year ago

Yes that's true. But the problem is to tell the jdbc driver which time zone it should use to save the timestamp. The default is to use the local time zone but i want utc to be used. Therefore the Calendar parameter to getTimestamp/setTimestamp exists which is ignored by MySQL in default. See my own answer for details.

Olaf Over a year ago

I see. It looks like the issue that you faced is quite MySQL-specific. I've added mysql tag to your question. Please consider editing the title of the question to reflect that it's MySQL-pertinent.

Werzi2001 Over a year ago

I changed the title. As i wrote the post i didn't know it was MySQL specific yet.

Collectives™ on Stack Overflow

JDBC/MySQL: Save timestamp always using UTC

2 Answers 2

2 Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related