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
– Jon Skeet, Commented Aug 13, 2013 at 14:04
What is mysql's timezone setting? Use this to check: stackoverflow.com/a/930914/791406 — raffian
– raffian, Commented Aug 13, 2013 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
– Somnath Guha, Commented Aug 13, 2013 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
– Somnath Guha, Commented Aug 13, 2013 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. Check this answer and this answer to learn how to use java.time API with JDBC. — Arvind Kumar Avinash
– Arvind Kumar Avinash, Commented Jul 20, 2021 at 19:02

Jon Skeet · Accepted Answer · 2013-08-13 14:42:38Z

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 · Accepted Answer · 2013-08-13 15:22:25Z

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, 2013 at 15:22

answered Aug 13, 2013 at 14:51

RandomSeed

29.9k6 gold badges54 silver badges91 bronze badges

2 Comments

Somnath Guha Over a year ago

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.

RandomSeed Over a year ago

@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.

Collectives™ on Stack Overflow

Java & MySQL timezone issue

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related