title: What's the right way to handle UTC date-times using Java, iBatis, and Oracle?
body: <p>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.</p>
  
  <p>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:</p>
  
  <pre><code>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 &gt;= start_time)
  );
  </code></pre>
  
  <p>The oracle <code>asset_time</code> constraint is firing for programs that straddle the US central daylight savings time adjustment this upcoming Sunday morning, 11/1/2009.</p>
  
  <p>I have this data transfer object (the Dates are java.util.Dates):</p>
  
  <pre><code>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; }
   [...]
  }
  </code></pre>
  
  <p>And in the iBatis SQL map I have this statement that inserts an Asset DTO into the Oracle Asset table:</p>
  
  <pre><code>&lt;insert id="Asset.insert" parameterClass="com.acme.Asset"&gt;
      insert into Asset 
          ( asset_id, [...] start_time, end_time )
      values
          ( #asset_id#, [...] #start_time#, #end_time# )
  &lt;/insert&gt;
  </code></pre>
  
  <p>On the Java side I've verified that I'm giving iBatis the correct UTC date input via this pre-insert assertion, which <em>isn't</em> thrown:</p>
  
  <pre><code>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.");
  }
  </code></pre>
  
  <p>Just before the Oracle constraint failure the above code prints:</p>
  
  <pre><code>Inserting asset EP011453960004
    2009-11-01T06:30:00Z--2009-11-01T07:00:00Z
  </code></pre>
  
  <p>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.</p>
  
  <p>iBatis reports the Oracle constraint failure (edited):</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>JDBCReadWrite.Driver        = oracle.jdbc.OracleDriver
  JDBCReadWrite.ConnectionURL = jdbc:oracle:thin:@localhost:1521:XE
  </code></pre>
  
  <p>What's the correct way to ensure that this code is purely UTC?</p>
  
  <p>Thanks in advance!</p>