title: Optimize JDBC Oracle query to give consistent results across time zones and day light saving
body: <p>At our company Java web applications (Web sphere is the app server) run in windows boxes located in Arizona (MST). Our oracle database is in Chicago (CST). The following query is consistent all the times except between 11 PM and 12 AM MST. When I say consistent what I mean is if I run the query against the database via SQLDeveloper and the JDBC Query via java program will give the same result.</p>
  
  <pre><code>select extract(day from(systimestamp - to_timestamp('1970-01-01', 'YYYY-MM-DD'))) as systimestamp2 from dual;
  </code></pre>
  
  <p><strong>Sample failure case as example:</strong>
  When I run the above query at 11 PM MST on 12th July 2016 the result from the Java program is <code>16994</code> and the result from the sql developer is <code>16995</code>. Other than this time interval of 1 hour between 11 PM MST and 12 AM MST they are equal. So it is working as expected before 11 PM MST and after 12 AM MST.</p>
  
  <p>I have tried various variants of query as shown below and none of them worked.</p>
  
  <pre><code>select extract(day from(sys_extract_utc(current_timestamp) - sys_extract_utc(to_timestamp('1970-01-01', 'YYYY-MM-DD')))) as systimestamp2 from dual;
  
  select extract(day from(sys_extract_utc(systimestamp) - sys_extract_utc(to_timestamp('1970-01-01', 'YYYY-MM-DD')))) as systimestamp2 from dual;
  </code></pre>
  
  <p>I found similar threads like <a href="https://stackoverflow.com/questions/13238698/oracles-date-and-time-without-daylight-savings-via-jdbc">this</a> through which I got to know that the fate of the JDBC query depends on the timezone of the JVM. So when I changed the JVM timezone to MDT or UTC the problem is solved. But this is not acceptable workaround at our company since it needs regression testing and budget. </p>
  
  <p>So what I am looking for is <strong>Is there way to modify the query to make it consistent across time zones and day light savings</strong>.</p>
  
  <p>PS: Just thought of including my complete query so that it might be clear what I am trying to achieve</p>
  
  <pre><code>select extract(day from(systimestamp - to_timestamp('1970-01-01', 'YYYY-MM-DD'))) * 86400000 + to_number(to_char(sys_extract_utc(systimestamp), 'SSSSSFF3')) as systimestamp2 from dual;
  </code></pre>
  
  <p>Adding my Java Code to demonstrate the sql query execution via Java</p>
  
  <pre><code>public long getSystemTimestampInMilliSeconds() {
          Connection conn = null;
          ResultSet rs = null;
          CallableStatement cs = null;
          try {
              conn = dataSource.getConnection();
              String sql = "select extract(day from(systimestamp - to_timestamp('1970-01-01', 'YYYY-MM-DD'))) * 86400000 + to_number(to_char(sys_extract_utc(systimestamp), 'SSSSSFF3')) as systimestamp2 from dual";
              cs = conn.prepareCall(sql);
              rs = cs.executeQuery();
              while (rs.next()) {
                  return rs.getLong(1);
              }
          } catch (Exception e) {
              LogFacility.logError( LogFacility.LOG_LEVEL_ERROR, "Exception in UtilityStore.getSystemTimestampInMilliSeconds " + e.getMessage() );
          } finally {
              close(rs, cs, conn);
          }
  
          return 0;
  }
  </code></pre>