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!

Solution

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.");
    }
}

Comments

[Jim Ferrans]

Thanks for the help! I think now that this issue is at the boundary between iBatis and JDBC. java.sun.com/j2se/1.3/docs/guide/jdbc/spec2/… indicates that JDBC does default timezone mapping, and if I force the JVM default timezone to GMT the problem does go away. The iBatis default TypeHandlerCallback for Date relies on this default behavior, so I believe I need to override it with one that calls ResultSet.getDate(int, Calendar) and PreparedStatement.setDate(int, Calendar) to force GMT.

[Jim Ferrans]

@jhartelt: +1: I think you were close to the mark, though it's not Oracle itself making the conversions, but the Oracle JDBC driver implementing proper JDBC behavior. Thanks again!

[DDK]

which file do you add this typehandler tag ? Is it supported in ibaits 2.3 ?