CODEWITHSUNDEEP
javamysqldatetime
jn1kk
jn1kk

Reputation: 5112

Date Issue From MySQL To Java (TimeZone Addition)

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.

Upvotes: 1

Views: 1114

Answers (4)

Leon
Leon

Reputation: 198

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

Upvotes: 0

jn1kk
jn1kk

Reputation: 5112

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")));

Upvotes: 2

Charles Wood
Charles Wood

Reputation: 882

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.

Upvotes: 0

nkukhar
nkukhar

Reputation: 2045

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.

Upvotes: 0

Related Questions