Convert joda.time.DateTime to java.sql.Date and retain time zone

date, java, jdbc, jodatime

Solution

Losing Time

One problem may be that java.sql.Date are supposed to be…

'normalized' by setting the hours, minutes, seconds, and milliseconds to zero in the particular time zone with which the instance is associated.

…according to the documentation. That means, the time portion of the date-time is being cleared from your java.util.Date or Joda-Time DateTime objects.

No Time Zone

As the correct answer by Gilbert Le Blanc notes, both java.util.Date and java.sql.Date have no concept of time zone internally. They store the number of milliseconds since the Unix epoch.

Those classes pull a nasty trick: Their `toString` methods apply your JVM's default time zone to the rendering of the string. Very confusing. The `Date` object has no time zone, yet when displayed as a string you see a time zone.

If your java.util.Date object contains the number of 1344902399000L milliseconds since the epoch (1970 start), that means `2012-08-13T23:59:59.000Z` in UTC/GMT. But if your JVM believes itself to be in France with Daylight Saving Time (DST) in effect, you'll see 2 hours ahead of UTC/GMT: `2012-08-14T01:59:59.000+02:00` described in that class' awful string format. The same moment of time has different day-of-month meaning (13 vs 14) in different time zones, with the clock-on-the-wall being past midnight.

Joda-Time To The Rescue

The Joda-Time 2.4 library can be helpful here. Pass either the java.sql.Date or java.util.Date object to a DateTime constructor along with the UTC time zone object to get a clear picture of the value with which you are struggling.

java.util.Date date = new java.util.Date( 1390276603054L );
DateTime dateTimeUtc = new DateTime( date, DateTimeZone.UTC );
System.out.println( "dateTimeUtc: " + dateTimeUtc );

When run…

2014-01-21T03:56:43.054Z

To convert the other direction from Joda-Time to java.util.Date…

java.util.Date date = myDateTime.toDate();

To convert the other direction from Joda-Time to java.sql.Date…

java.sql.Date date = new java.sql.Date( myDateTime.getMillis() );

Update – java.time

The Joda-Time project is now in maintenance mode, with the team advising migration to the java.time classes.

The equivalent of `java.util.Date` is `Instant`. The `Instant` class represents a moment on the timeline in UTC with a resolution of nanoseconds (up to nine (9) digits of a decimal fraction).

Instant instant = Instant.ofEpochMilli( 1390276603054L );

Apply a time zone, `ZoneId`, to produce a `ZonedDateTime` which is akin to a `java.util.Calendar` and a Joda-Time `DateTime`.

ZoneId z = ZoneId.of( "Europe/Kaliningrad" );
ZonedDateTime zdt = instant.atZone( z );

Now extract a date-only value, the date portion of that `ZonedDateTime`, as a `LocalDate`. The `LocalDate` class represents a date-only value without time-of-day and without time zone. So `LocalDate` is what `java.sql.Date` is pretending to be: a date-only value.

LocalDate localDate = zdt.toLocalDate() ;

In JDBC 4.2 and later, you can use the java.time types directly with a compliant driver via `PreparedStatement::setObject` and `ResultSet::getObject`.

myPreparedStatement.setObject( … , localDate );

…and…

LocalDate ld = myResultSet.getObject( … , LocalDate.class );

For an older non-compliant driver, convert briefly to a `java.sql.Date` object to/from a `LocalDate` by using new methods added to the old class: `toLocalDate` and `valueOf( LocalDate )`.

Problem

I have the following scenario: - Swing control that returns a `Calendar` object - Intermediate `DateTime` object that I use to do heavy date/time manipulation (joda) - Database connection (`OraclePreparedStatement`) that only takes a `java.sql.Date` object My problem is that the `Calendar` and `DateTime` objects are properly displaying the date in GMT (which I want), but when I convert to `java.sql.Date` in order to send to the database, the date is converted to the local time zone. For example: - `Calendar` and `DateTime` are 2012-08-13T23:59:59.000Z (correct GMT) - Resulting `java.sql.Date` is 2012-08-14 (incorrect local UTC+2 date) Below is the code I'm using to do the conversion. ``` DateTime dateGmt = new DateTime(calendarGmt.getTimeInMillis(), DateTimeZone.UTC); java.sql.Date sqlDate = new java.sql.Date(dateGmt.getMillis()); ``` I don't know how to create a `java.sql.Date` object while retaining the correct time zone. It's also entirely possible that I'm doing an incorrect conversion.

Original source