Date Issue From MySQL To Java (TimeZone Addition)

Question

I have a field called startDate with type TIMESTAMP I get from MySQL InnoDB database:

SELECT startDate FROM jn.all WHERE id IN(115)

=> 2012-07-28 00:00:00

In Java I do:

java.util.Date d = resultSet.getDate("startDate");
SimpleDateFormat tsmpFormatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
tsmpFormatter.setTimeZone(TimeZone.getTimeZone("UTC"));
System.out.println(tsmpFormatter.format(d));

I get:

=>2012-07-28 04:00:00

There are 4 hours from UTC to EST. Database time is UTC.

SELECT now();

=> 2013-07-29 21:46:26

While my current EST time is 17:46:26

Why am I getting this 4 hour difference even though I am using UTC everywhere? Thanks!

EDIT:

I may have found the issue.

http://dev.mysql.com/doc/refman/5.1/en/datetime.html

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

But what is this current time zone? Server should be in UTC.

Did you figure out why the current time zone is EST instead of UTC? — Charles Wood
– Charles Wood, Commented Jul 30, 2013 at 13:56

jn1kk · Accepted Answer · 2013-07-29 22:06:18Z

2

Sorry everyone, found my own answer. This solution looks absurd to me, but what can you do.

I had to pass a Calendar when getting the data from the result set.

Date begin = resultSet.getDate("startDate", new GregorianCalendar(TimeZone.getTimeZone("UTC")));

answered Jul 29, 2013 at 22:06

jn1kk

5,1422 gold badges48 silver badges74 bronze badges

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

Comments

nkukhar · Accepted Answer · 2013-07-29 21:55:52Z

0

Try to use:

java.sql.Timestamp ts = resultSet.getTimestamp("mvStartDate");

resultSet.getDate() returns java.sql.Date and not java.util.Date

With java.util.Date you loose time information.

edited Jul 29, 2013 at 21:55

answered Jul 29, 2013 at 21:48

nkukhar

2,0652 gold badges19 silver badges38 bronze badges

Comments

Charles Wood · Accepted Answer · 2013-07-29 22:04:30Z

0

I'm sure you've read this elsewhere already, but you might consider looking into Joda Time, which is aware of time zones and can ignore them or not.

It's a little bit of a mental investment to get into, but it's worth it in terms of flexibility and power.

answered Jul 29, 2013 at 22:04

Charles Wood

9229 silver badges24 bronze badges

2 Comments

jn1kk Over a year ago

I am positive JodaTime would exhibit the same behavior. It is the MySQL driver that is trying to do something clever when it shouldn't. Because the milliseconds it gives me since 1970, are not since UTC, but EST (+4 hours -> so 4AM UTC). See my answer.

Charles Wood Over a year ago

Ah, that's odd. I have to admit I haven't used the TIMESTAMP column format. But hey, what date/time question would be complete without a blind "use joda!" answer? ;)

Leon · Accepted Answer · 2013-07-29 22:59:22Z

0

Beforeusing java.util.date, you should be aware of default timezone. your code could be:

    ...
    TimeZone.setDefault(TimeZone.getTimeZone("UTC"));
    ...
    java.util.Date d = resultSet.getDate("mvStartDate");
    SimpleDateFormat tsmpFormatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    tsmpFormatter.setTimeZone(TimeZone.getTimeZone("UTC"));
    System.out.println(tsmpFormatter.format(d));
    ...

answered Jul 29, 2013 at 22:59

Leon

2082 silver badges7 bronze badges

Collectives™ on Stack Overflow

Date Issue From MySQL To Java (TimeZone Addition)

4 Answers 4

Comments

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related