Java & MySQL timezone issue

Question

I am trying to convert a string date value in java to date and trying to store it in a mysql table.

Below the code snippet:

DateFormat dfm = new SimpleDateFormat("MM/dd/yyyy HH:mm:ss");
String string = "07/24/2013 17:57:52 UTC";
Date a = dfm.parse(string);
System.out.println(a);

My problem is that the above code always returns the following console output: Wed Jul 24 17:57:52 PDT 2013

I don't know why the time zone is getting changed, more over when I am trying to put this into the database then it is storing it in '2013-07-24 17:57:52' format. I am not sure why the above code is returning me timezone in PDT?

Can you guys please explain me that? My intent is to store a UTC date which will come as an input and store it into the MySQL timestamp field.

Thanks in Advance.

Please show us how you're storing this in the database. Additionally, does your input *have* to have " UTC" at the end, or could that be dropped? (We don't know where your input is coming from.) Most of the rest is easy to fix... — Jon Skeet, Aug 13 '13 at 14:04
What is mysql's timezone setting? Use this to check: http://stackoverflow.com/a/930914/791406 — raffian, Aug 13 '13 at 14:05
@JonSkeet Thanks for you reply. I can remove the UTC programmatically. But how that will going to help. Removing UTC from the String variable is still showing the same as above. Also I am using JPA to persist the data. "testdate testobj = new testdate(); testobj.setAddDate(new Timestamp(a.getTime())); em.persist(testobj);" — Somnath Guha, Aug 13 '13 at 14:32
@raffian Thanks for reply too..I know I can set the default timezone in mysql. But is there any way we can skip that and do some thing programmatically. — Somnath Guha, Aug 13 '13 at 14:36
The `java.util` Date-Time API and their formatting API, `SimpleDateFormat` are outdated and error-prone. It is recommended to stop using them completely and switch to the [modern Date-Time API](https://www.oracle.com/technical-resources/articles/java/jf14-Date-Time.html). Check [this answer](https://stackoverflow.com/a/67752047/10819573) and [this answer](https://stackoverflow.com/a/67505173/10819573) to learn how to use `java.time` API with JDBC. — Arvind Kumar Avinash, Jul 20 '21 at 19:02

score 1 · Answer 1 · answered Aug 13 '13 at 14:42

Right, if you can fix the string to not include the time zone, it's simpler. Firstly, you need to understand that a Date object doesn't contain a time zone at all - it's just a point in time.

Next, as we're trying to parse a date/time specified in UTC, you should set that in the SimpleDateFormat:

DateFormat dfm = new SimpleDateFormat("MM/dd/yyyy HH:mm:ss");
dfm.setTimeZone(TimeZone.getTimeZone("UTC"));

Now at that point I'd hope that JPA would do the right thing. You'd at least be passing the right value to the Timestamp constructor.

However, this part of the MySQL documentation makes me nervous:

MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.)

It sounds like you may want to use a DATETIME field instead, to stop this time zone conversion. Setting the time zone of the connection to UTC would help when storing it, but you'd still need to worry about what would happen when fetching. (Databases are pretty messed up when it comes to date/time types, IMO. This is just another example of that...) On the other hand, if you're fetching the data back with Java as well, I'd hope that it would just work transparently. It's probably worth at least trying that...

RandomSeed · Answer 2 · 2013-08-13T15:22:25.040

0

You are missing the timezone part in your format string. "UTC" is therefore ignored. Try this:

DateFormat dfm = new SimpleDateFormat("MM/dd/yyyy HH:mm:ss z"); // added "z"
// then the rest of your code as-is
String string = "07/24/2013 17:57:52 UTC";
Date a = dfm.parse(string);
System.out.println(a); // prints "Wed Jul 24 19:57:52 CEST 2013" for me

Further to @JonSkeet 's word of warning, MySQL "current time zone" can be checked or changed through the time_zone session variable.

Either make sure your session is set to UTC (SET time_zone = '+0:00'), or use a DATETIME type instead.

edited Aug 13 '13 at 15:22

answered Aug 13 '13 at 14:51

RandomSeed

29,301
6
52
87

I don't want to convert it in local time zone. I want to persist the data as it is...should I convert this value in timestamp. – Somnath Guha Aug 13 '13 at 16:10
@SomnathGuha If you want to deal with time zones at application level (only), then do *not* use the `TIMESTAMP` type, but the `DATETIME` type instead. – RandomSeed Aug 13 '13 at 16:22

Java & MySQL timezone issue

2 Answers2