DateTime to java.sql.timestamp conversion is adding +1 hour

Question

I am calling my rest api with following url localhost:8080/api/2016-05-30T10:30:00-05:00/3

The api receives date as String and then convert it into jodatime datetime object like dateTime = DateTime.parse(date); ... debugging this code shows that its resulting in expected value.

However, when i am converting this date to java.sql.timestamp like Timestamp ts = new Timestamp(dateTime.getMillis()); ... the resulting time is 2016-05-30 11:30:00.0 ... why is it adding +1 hour to the time and whats the proper way to convert ?

SOME BACKGROUND

I have saved the time as timestamp in sql table. with timezone (as a string +4:00 or -5:00 for example) in a separate column.

I would receive an ISO8601 time in my url path parameter and based on that I have to fetch the record from db. For that, I will be using two comparison. 1 to match the time and 2 to match the timezone.

You might want to include information about the timezone. It can often have ... interesting side effects. — Matthieu, Apr 24 '16 at 17:42
What information shall i add ? I am not doing anything explicit per say ... my system is set to EDT Time zone though — Em Ae, Apr 24 '16 at 17:43
I'm not familiar with jodatime but `java.sql.Timestamp(long)` expects the argument to be in GMT timezone and I don't see any timezone specified in the [`BaseDateTime.getMillis()` javadoc](http://joda-time.sourceforge.net/apidocs/org/joda/time/base/BaseDateTime.html#getMillis%28%29), though it appears to be using ISO8601, which Z means UTC, which is equivalent ot GMT... — Matthieu, Apr 24 '16 at 17:51
You are currently on daylight savings time, which advances your clock one hour... can't be a coincidence. — Bohemian, Apr 24 '16 at 18:44
I know but what i am trying to figure out is an absoluate way to convert JodaTime to `java.sql.timestamp` so that i can execute the query. — Em Ae, Apr 24 '16 at 18:46
@EmAe it seems there is none, as DST messes it up (not handled in ISO 8601). — Matthieu, Apr 25 '16 at 03:01
... or actually you're doing it right: using the `Timestamp(long)` constructor. — Matthieu, Apr 25 '16 at 03:18
I have updated my answer with information about `Timezone.inDaylightTime()` method that might be useful in your case. — Matthieu, Apr 25 '16 at 13:12

score 1 · Answer 1 · edited May 23 '17 at 10:29

The ISO 8601 states that

... the time in New York in winter is UTC−05:00

So it seems daylight saving time is not included in the date string, which means what you get is the correct time, as per the defined timezone (-05:00). New York summer time should be -04:00, as DST is not part of ISO 8601.

You could add an extra DST column in your database or add it as a parameter, or go through the process of checking whether you are in DST, which varies country to country (e.g. Qatar does not uses it) and year by year (e.g Russia has abandoned DST a few years ago), so it's not a viable option...

EDIT: Another option would be for you to test if the Timezone is currently in DST, as indicated in another answer. You could then apply the -1h offset.

Note that Joda has a minusHours() method you can use, if inDaylightTime() returns true.

DateTime to java.sql.timestamp conversion is adding +1 hour

1 Answers1