What's the right way to handle UTC date-times using Java, iBatis, and Oracle?

Question

I'm coming up against an unexpected daylight savings time problem in code I thought was purely UTC. I'm using Java 1.6, the iBatis SQL mapper (2.3.3), and Oracle XE (an eval version of Oracle 10.2) with the Oracle thin driver.

The database contains a table that represents a television broadcast schedule. Each "Asset" (program) has a start_time and and end time. Here's the relevant slice:

create table Asset
(
 asset_id      integer not null, -- The unique id of the Asset.
 [...] 
 start_time    timestamp,        -- The start time.
 end_time      timestamp,        -- The end time.
 [...] 

 constraint asset_primary_key    primary key (asset_id),
 constraint asset_time           check (end_time >= start_time)
);

The oracle asset_time constraint is firing for programs that straddle the US central daylight savings time adjustment this upcoming Sunday morning, 11/1/2009.

I have this data transfer object (the Dates are java.util.Dates):

public class Asset 
{
 protected Long    asset_id;
 [...]
 protected Date    start_time;
 protected Date    end_time; 

 public Date       getStart_time()     { return start_time; }
 public Date       getEnd_time()       { return end_time; }

 public void setStart_time(Date start_time) { this.start_time = start_time; }
 public void setEnd_time(Date end_time)     { this.end_time = end_time; }
 [...]
}

And in the iBatis SQL map I have this statement that inserts an Asset DTO into the Oracle Asset table:

<insert id="Asset.insert" parameterClass="com.acme.Asset">
    insert into Asset 
        ( asset_id, [...] start_time, end_time )
    values
        ( #asset_id#, [...] #start_time#, #end_time# )
</insert>

On the Java side I've verified that I'm giving iBatis the correct UTC date input via this pre-insert assertion, which isn't thrown:

System.err.println("Inserting asset " + program_id);
System.err.println("  "+asset.getStart_time_str()+"--"+asset.getEnd_time_str());
if ( !asset.getEnd_time().after(asset.getStart_time())) {
 System.err.println("Invalid datetime range in asset.");
 throw new AssertionError("Invalid datetime range in asset.");
}

Just before the Oracle constraint failure the above code prints:

Inserting asset EP011453960004
  2009-11-01T06:30:00Z--2009-11-01T07:00:00Z

I'm in the US central time zone, GMT -5:00, so this program starts at 1:30am and ends at 2:00am. The daylight savings change hits at 2:00am and turns the clock back to 1:00am.

iBatis reports the Oracle constraint failure (edited):

2009-10-30 22:58:42,238  [...] Executing Statement:
    insert into Asset ( asset_id, [...] start_time, end_time )
         values       ( ?, [...] ?, ? )  
2009-10-30 22:58:42,238  [...] Parameters: 
    [EP011453960004, [...] 2009-11-01 01:30:00.0, 2009-11-01 01:00:00.0]
2009-10-30 22:58:42,238  [..] Types: 
    [java.lang.Long, [...] java.sql.Timestamp, java.sql.Timestamp]
2009-10-30 22:58:42,285  [...] - Failed with a SQLException:   
--- The error occurred in com/acme/data/dao/Asset-Write.xml.  
--- The error occurred while applying a parameter map.  
--- Check the Asset.insert-InlineParameterMap.  
--- Check the statement (update failed).  
--- Cause: java.sql.SQLException: ORA-02290: check constraint (ACME.ASSET_TIME)
                                             violated

You'll notice that on the Oracle side, it's seeing the start_time/end_time with the daylight savings time adjustment, so something in the iBatis mapping logic or the Oracle driver isn't doing what I expected. The driver is ojdbc14.jar, the thin driver:

JDBCReadWrite.Driver        = oracle.jdbc.OracleDriver
JDBCReadWrite.ConnectionURL = jdbc:oracle:thin:@localhost:1521:XE

What's the correct way to ensure that this code is purely UTC?

Thanks in advance!

Answer 1

Usually, Oracle converts date/time values from the client timezone to the server timezone, when storing data. And backwards, when reading it out again.

If you want the date/time values to be stored un-altered, you might want to use a variant of the timezone data type, the "TIMESTAMP WITH TIME ZONE Datatype", which lets you store the time zone with the value. You can find some info here in the Oracle SQL data type doc. Just search for the "with timezone" part.

Answer 2

I have a solution which seems to do the trick. Even though the application and the database used types that store time offsets from midnight on 1/1/1970 in GMT, the JDBC specification calls for applying an adjustment from/to the JVM's default timezone going in/out. And iBatis maps dates using the JDBC default. The adjustments were always symmetrical and therefore harmless as long as the data didn't cross a daylight savings time boundary, or if the machine or JVM were set to GMT by default.

As an experiment I switched the JVM default timezone to GMT:

TimeZone.setDefault(TimeZone.getTimeZone("UTC"));

and this solved the problem, though in a very heavy-handed way (other code in the JVM may not expect this).

But iBatis allows you to override the default type handling, at any level of granularity. I wrote a GMT-preserving type handler and registered it for all my java.util.Dates:

<typeHandler callback="com.acme.GMTDateTypeHandler" javaType="java.util.Date"/>

My type handler looks like this:

public class GMTDateTypeHandler implements TypeHandlerCallback
{     
    @Override
    public void setParameter(ParameterSetter setter, Object parameter) 
        throws SQLException
    {
        java.util.Date date = (java.util.Date) parameter;
        if ( date == null )
            setter.setNull(Types.TIMESTAMP);
        else
        {
            Timestamp timestamp = new Timestamp(date.getTime());
            Calendar calendar = Calendar.getInstance(TimeZone.getTimeZone("UTC"));
            setter.setTimestamp(timestamp, calendar);
        }
    }

    @Override
    public Object getResult(ResultGetter getter) throws SQLException
    {
        Calendar calendar = Calendar.getInstance(TimeZone.getTimeZone("UTC"));
        return getter.getTimestamp(calendar);
    }

    @Override
    public Object valueOf(String s)
    {
        throw new UnsupportedOperationException(
            "GMTDateTypeHandler.valueOf() is not supported.");
    }
}