Saving dates in DB normalized to UTC timezone

[R. Rajesh Jeba Anbiah]

We're trying to save all dates normalized to UTC in database. Found this link https://groups.google.com/forum/?fromgroups=#!topic/play-framework/Er46pB5t_OU, but that's not helpful as it suggests changing environment than code.

So, far these are the results for the approaches we tried:

1. play run -Duser.timezone=UTC

      This works fine (converts the date to UTC and saves in DB; when retrieving from DB, it keeps the date in UTC). But, we're looking for runtime/programmatic approach.

2.  TimeZone.setDefault(TimeZone.getTimeZone("UTC")); in @OnApplicationStart bootstrap job

      This converts the date to UTC and saves in DB. But, when fetching, it again does the conversion and date is going back by offset. For example, for UTC+05:30, when saving to DB it minuses 5:30 and when fetching it again reduces 5:30

3. System.setProperty("user.timezone", "UTC");  in @OnApplicationStart bootstrap job. As per docs, this is the programmatic approach for #1.

      No conversion is done here.

Can anybody shares some insights here? TIA 

[Ciaran Treanor]

If possible I'd use Joda Time for time and date handling rather than Java Calendar & Date (Google to find out why Calendar & Date are very broken)

To do this, add the following to your dependencies.conf

    - joda-time -> joda-time-hibernate 1.3

Then update your models to use Joda DateTime's instead of java.util.Date and annotate those fields as shown below.

import org.hibernate.annotations.Type;

public class MyModel extends Model {
  public String name;
  @Type(type = "myutils.PersistentDateTimeUTC")
  public DateTime created;
}

Create the myutils.PersistentDateTimeUTC class. Using this ensures that DateTime timezone is set to UTC when retrieving dates from the database. Remember, the database only stores dates - it doesn't know anything about timezones.

package myutils;


/**
 * Persist {@link org.joda.time.DateTime} via hibernate interpreting dates
 * as UTC (rather than local JVM TZ) when retrieving them from database.
 */
public class PersistentDateTimeUTC extends PersistentDateTime {

    public Object nullSafeGet(ResultSet resultSet, String string) throws SQLException {
        Object timestamp = StandardBasicTypes.TIMESTAMP.nullSafeGet(resultSet, string);
        if (timestamp == null) {
            return null;
        }


        return new DateTime(timestamp, DateTimeZone.UTC);
    }

}

When creating DateTime objects you need to ensure that their timezone is set to UTC before persisting any models that reference them. You can do this on a case-by-case basis by setting DateTimeZone.UTC as the timezone when creating a DateTime instance. Alternatively you can ensure set the Joda Time default timezone to UTC by doing DateTimeZone.setDefault(DateTimeZone.UTC) in an @OnApplicationStart job.

ct

[R. Rajesh Jeba Anbiah]

Thanks a lot for the wonderful suggestion. Will try this now. Thanks again