About

Jadira is the home for Sousan and Chris Pheby's open source projects. These are reusable open source Java modules that provide first class solutions using the most effective current JEE technologies.

Search
Tag Cloud
...
Login
« Configuring Open Source JTA Transaction Managers with Spring | Main | Testing Classes using java.util.Date with JMockit »
Saturday
May012010

java.sql.Date Type(s) and the Offsetting Problem

Release 1.1 of User Type attempts to address the offsetting problem for java.sql.Date, Time, Timestamp, as well as the Joda Time and JSR 310 types. For example, here is how the problem is approached for Joda Time and JSR 310 Timestamp types:

    @Override
    public LocalTime fromNonNullValue(Timestamp value) {
        return LOCAL_DATETIME_FORMATTER.parseDateTime(value.toString()).toLocalTime();
    }

    @Override
    public Timestamp toNonNullValue(LocalTime value) {

        String formattedTimestamp = LOCAL_DATETIME_FORMATTER.print(value);
        ...
        final Timestamp timestamp = Timestamp.valueOf(formattedTimestamp);
        return timestamp;
    }

The remainder of this article discusses what the offsetting problem is and why User Type has been implemented to provide an approach for addressing it when using Hibernate with Joda Time or JSR 310.

JDBC's support for Timezones in Date, Time and Timestamp types was added in JDBC 2.1 released in 1999 (over a decade ago at the time of writing). The specification describes the rationale:

The JDBC API follows the Java platform’s approach of representing dates and times as a millisecond value relative to January 1, 1970 00:00:00 GMT. Since most databases don’t support the notion of a time zone, the JDBC 2.1 core API adds new methods to allow a JDBC driver to get/set Date, Time, and Timestamp values for a particular time zone using a Calendar.

For example,
ResultSet rs;
...
Date date1 = rs.getDate(1);

returns a Date object that wraps a millisecond value which denotes a particular date, like January 3, 1999, and a normalized time 00:00:00 in the default time zone. The time component of the Date is set to zero in the default time zone since SQL DATE values don’t have a time component. Since a Calendar was not supplied explicitly to getDate(), the default time zone (really the default Calendar) is used by the JDBC driver internally to create the appropriate millisecond value assuming that the underlying database doesn’t store time zone information.


The following example retrieves a date value in GMT—Greenwich Mean Time.

ResultSet rs;
...
TimeZone.setDefault(TimeZone.getTimeZone("GMT"));
Calendar cal = Calendar.getInstance();
Date date2 = rs.getDate(1, cal);

In the example above, a Calendar is passed explicitly to getDate() to inform the JDBC driver how to calculate the appropriate millisecond value. Note that the same result could have been achieved by simply changing the default time zone, and not passing the Calendar explicitly since the JDBC driver will use the default time zone by default.

...

from 10.5 Date, Time, and Timestamp, p47, JDBC 2.1 API Version 1.1, Seth White and Mark Hapner. October 5, 1999 4:39 pm

The rationale for this behaviour seems sensible enough, but unfortunately there is a gap between this typical behaviour and practical real-life needs. The problem lies in the use of the default zone. Many databases serve many clients, and these are not always located in the same zone as the database. In fact, it is quite comment for clients to be running in a variety of different zones.

What this results in is unexpected behaviour when reading and writing from the database. For example, suppose my database is configured using UTC but I am interacting with the database from a client whose default zone is British Summer Time - what kind of behaviour can I expect?

Well, supposing a SQL Timestamp row contains 2010-08-01T10:10:10.111Z, then when I read this data from the database, it will unmarshal into a java.sql.Timestamp with the following literal value: 2010-08-01T09:10:10.111Z. This is the same actual timestamp but rendered in the BST zone. Similar behaviour occurs when I write values.

In itself, this behaviour could actually be appropriate, after all these Java types have both a millisecond value and a zone. Nevertheless, this behaviour can be surprising at the best of times. The availability of new Java date and time APIs has made this behaviour even more desirable. Joda Time and JSR 310 model types including combinations of dates, times with and without zones, millisecond instances, and periods of elapsed time. In the case local dates, times and datetimes - that is points in time without zone information, the desired behaviour is to be able to persist the value as literally supplied.

This problem is not new. It is discussed in some detail in the Hibernate forums, JSR 310 mailing list, Stackoverflow:

For more information on User Type visit the Sourceforge Project page.

PrintView Printer Friendly Version

EmailEmail Article to Friend

References (4)

References allow you to track sources for this article, as well as articles that were written in response to this article.
  • Response
    Response: [WL-558] 2
    null /** * Our {@link Technology#Oracle} databases store "dates" in UTC. * Hibernate assumes that the DB time zone is the application's time zone. * The UTCDate {@link UserType} ensures that {@link Date}s in the POJO's have the same value as in...
  • Response
    java.sql.Date Type(s) and the Offsetting Problem - Articles - Jadira
  • Response
    Response: forum bola
    java.sql.Date Type(s) and the Offsetting Problem - Articles - Jadira
  • Response
    kontakt

Reader Comments (4)

2010-08-01T10:10:10.111Z and 2010-08-01T09:10:10.111Z are not "the same actual timestamp but rendered in [different timezones]" because the Z digit means UTC, so these two timestamps are actually in the same timezone, and one hour apart.

April 18, 2011 | Unregistered CommenterDaniel

Daniel,

Thanks for your clarification, however

2010-08-01T10:10:10.111 in BST is equivalent to 2010-08-01T09:10:10.111 in UTC. The two date-times are literally constructed from '2010-08-01T09:10:10.111' but subsequently offset according to your offset from UTC. As you rightly note they result in a value that in the same timezone is one hour apart.

Regards Chris

June 1, 2011 | Registered CommenterChris

Worth mentioning that when upgrading to Hibernate 4 from 3, we had a problem because dates were stored as Europe/London (therefore GMT and BST) which we needed to add the following Hibernate properties to work around:


<prop key="jadira.usertype.databaseZone">jvm</prop>
<prop key="jadira.usertype.javaZone">jvm</prop>

As per this: http://stackoverflow.com/questions/15544710/jadira-usertype-timezone#17872389

August 14, 2013 | Unregistered CommenterDavid Wood

Probably also worth mentioning that we later converted entirely to UTC representation in the DB (one big DB script) and avoided all the tricky problems caused by the "repeating hour" on changeover once a year!

September 10, 2014 | Unregistered CommenterDavid Wood

PostPost a New Comment

Enter your information below to add a new comment.

My response is on my own website »
Author Email (optional):
Author URL (optional):
Post:
 
Some HTML allowed: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <code> <em> <i> <strike> <strong>