Example of using JDBC Timezone

Posted by Steve Racanovic | Posted in | Posted on 11:46 AM

0

Recently I was working on a timezone issue there the values returned on the daylight saving change over where incorrect. Here is the example and steps to reproduce this problem.

There sql script looks like:

connect / as sysdba

drop user timezoneuser cascade;

grant connect,resource to timezoneuser identified by timezoneuser;

connect timezoneuser/timezoneuser

create table jdbc_test (
  sequence_id number(2),
  cbt timestamp,
  cbt_utc timestamp);

alter session set NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS';
alter session set NLS_TIMESTAMP_TZ_FORMAT ='MM/DD/YYYY HH24:MI:SS';
alter session set NLS_TIMESTAMP_FORMAT = 'MM/DD/YYYY HH24:MI:SS';

select * from nls_session_parameters;

insert into jdbc_test values(1,'04/05/2009 00:00:00','04/04/2009 13:00:00');
insert into jdbc_test values(2,'04/05/2009 01:00:00','04/04/2009 14:00:00');
insert into jdbc_test values(3,'04/05/2009 02:00:00','04/04/2009 15:00:00');
insert into jdbc_test values(4,'04/05/2009 02:00:00','04/04/2009 16:00:00');
insert into jdbc_test values(5,'04/05/2009 03:00:00','04/04/2009 17:00:00');
insert into jdbc_test values(6,'04/05/2009 04:00:00','04/04/2009 18:00:00');
insert into jdbc_test values(7,'04/05/2009 05:00:00','04/04/2009 19:00:00');
insert into jdbc_test values(8,'04/05/2009 06:00:00','04/04/2009 20:00:00');
insert into jdbc_test values(9,'04/05/2009 07:00:00','04/04/2009 21:00:00');
insert into jdbc_test values(10,'04/05/2009 08:00:00','04/04/2009 22:00:00');
insert into jdbc_test values(11,'04/05/2009 09:00:00','04/04/2009 23:00:00');
insert into jdbc_test values(12,'04/05/2009 10:00:00','04/05/2009 00:00:00');
insert into jdbc_test values(13,'04/05/2009 11:00:00','04/05/2009 01:00:00');
insert into jdbc_test values(14,'04/05/2009 12:00:00','04/05/2009 02:00:00');
insert into jdbc_test values(15,'04/05/2009 13:00:00','04/05/2009 03:00:00');
insert into jdbc_test values(16,'04/05/2009 14:00:00','04/05/2009 04:00:00');
insert into jdbc_test values(17,'04/05/2009 15:00:00','04/05/2009 05:00:00');
insert into jdbc_test values(18,'04/05/2009 16:00:00','04/05/2009 06:00:00');
insert into jdbc_test values(19,'04/05/2009 17:00:00','04/05/2009 07:00:00');

column seq format 999
column cbt format a20
column cbt_utc format a20
column difference format a30

select sequence_id as seq,cbt ,cbt_utc , cbt - cbt_utc as difference from jdbc_test;
and the java looks like:
package example;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;

import java.util.Date;
import java.util.Properties;

public class JDBCTimeStamp {

  private void queryDatabase() {
      Connection conn = null;

      String DRIVER = "oracle.jdbc.driver.OracleDriver";
      String connectionName = "jdbc:comp:env:sys";
      String URL_NAME = "jdbc:oracle:thin:@celcsol1.us.oracle.com:1521:tarsr3";
      String userName = new String("timezoneuser");
      String password = new String("timezoneuser");
      Properties prop = new Properties();
      prop.put("user", userName);
      prop.put("password", password);
      prop.put("connectionName", connectionName);
      try {
          try {
              Class.forName(DRIVER);
          } catch (Exception e) {
              System.out.println("Exception in Class.forName : " + e);
          }
          try {
              conn = DriverManager.getConnection(URL_NAME, prop);
          } catch (Exception e) {
              System.out.println("Exception in creating the connection : " + e);
          }

          String sqlString = " SELECT SEQUENCE_ID,CBT,CBT_UTC" + " FROM JDBC_TEST ";

          PreparedStatement ps = conn.prepareStatement(sqlString.toString());
          ResultSet rs = null;
          try {
              ps = conn.prepareStatement(sqlString);
              rs = ps.executeQuery();
              String sequenceId = null;
              Timestamp cbtTimeStamp = null;
              Timestamp cbtUtcTimeStamp = null;
              long longValueOfUtc;
              long longValueOfCbt;
              Date cbtDateValueFromLong;
              Date utcDateValueFromLong;

              System.out.println("ID \tCBT(local time) \t\t\tCBT_UTC(GMT) \t\t\tTime Difference");
              while (rs.next()) {
                  sequenceId = rs.getString(1);
                  cbtTimeStamp = rs.getTimestamp(2);
                  cbtUtcTimeStamp = rs.getTimestamp(3);
                  longValueOfUtc = cbtUtcTimeStamp.getTime();
                  longValueOfCbt = cbtTimeStamp.getTime();
                  cbtDateValueFromLong = new Date(longValueOfCbt);
                  utcDateValueFromLong = new Date(longValueOfUtc);
                  System.out.println(sequenceId + "\t" + cbtDateValueFromLong + "\t\t" + utcDateValueFromLong + "\t\t" + ((longValueOfCbt - longValueOfUtc) / 3600000));
              }
          } finally {
              if (ps != null)
                  ps.close();
              if (rs != null)
                  rs.close();
          }
      } catch (Exception e) {
          System.out.println("Exception :::" + e);
          e.printStackTrace();
      }
  }

  public static void main(String[] args) {
      JDBCTimeStamp main = new JDBCTimeStamp();
      main.queryDatabase();
  }
}
Upon running the java code using JDK 1.6 and 11g JDBC drivers (ojdbc6.jar), the following results are show:
ID   CBT(local time)                     CBT_UTC(GMT)                        Time Difference
1    Sun Apr 05 00:00:00 EST 2009        Sat Apr 04 13:00:00 EST 2009        11
2    Sun Apr 05 01:00:00 EST 2009        Sat Apr 04 14:00:00 EST 2009        11
3    Sun Apr 05 02:00:00 EST 2009        Sat Apr 04 15:00:00 EST 2009        12
4    Sun Apr 05 02:00:00 EST 2009        Sat Apr 04 16:00:00 EST 2009        11
5    Sun Apr 05 03:00:00 EST 2009        Sat Apr 04 17:00:00 EST 2009        11
6    Sun Apr 05 04:00:00 EST 2009        Sat Apr 04 18:00:00 EST 2009        11
7    Sun Apr 05 05:00:00 EST 2009        Sat Apr 04 19:00:00 EST 2009        11
8    Sun Apr 05 06:00:00 EST 2009        Sat Apr 04 20:00:00 EST 2009        11
9    Sun Apr 05 07:00:00 EST 2009        Sat Apr 04 21:00:00 EST 2009        11
10    Sun Apr 05 08:00:00 EST 2009        Sat Apr 04 22:00:00 EST 2009        11
11    Sun Apr 05 09:00:00 EST 2009        Sat Apr 04 23:00:00 EST 2009        11
12    Sun Apr 05 10:00:00 EST 2009        Sun Apr 05 00:00:00 EST 2009        11
13    Sun Apr 05 11:00:00 EST 2009        Sun Apr 05 01:00:00 EST 2009        11
14    Sun Apr 05 12:00:00 EST 2009        Sun Apr 05 02:00:00 EST 2009        10
15    Sun Apr 05 13:00:00 EST 2009        Sun Apr 05 03:00:00 EST 2009        10
16    Sun Apr 05 14:00:00 EST 2009        Sun Apr 05 04:00:00 EST 2009        10
17    Sun Apr 05 15:00:00 EST 2009        Sun Apr 05 05:00:00 EST 2009        10
18    Sun Apr 05 16:00:00 EST 2009        Sun Apr 05 06:00:00 EST 2009        10
19    Sun Apr 05 17:00:00 EST 2009        Sun Apr 05 07:00:00 EST 2009        10
This because the table was not created with local timezone. The column in the database is TIMESTAMP - which holds a non timezone specified value.

When jdbc extracts values from a TIMESTAMP column into a java.sql.Timestamp via the getTimestamp method, it ensures that the value, when printed, shows the same DATE/TIME as that stored in the database.

The correct way to create the table would be:
create table jdbc_test (
  sequence_id number(2),
  cbt TIMESTAMP WITH LOCAL TIME ZONE,
  cbt_utc TIMESTAMP WITH TIME ZONE);
Now that I am using 'WITH LOCAL TIME ZONE', I need change my java code to include the timezone in the connection like '((OracleConnection)conn).setSessionTimeZone("Australia/Victoria");'

So the java code would now look like:
...
try {
 conn = DriverManager.getConnection(URL_NAME, prop);
} catch (Exception e) {
 System.out.println("Exception in creating the connection : " + e);
}

((OracleConnection)conn).setSessionTimeZone("Australia/Victoria");
String sqlString = " SELECT SEQUENCE_ID,CBT,CBT_UTC" + " FROM JDBC_TEST ";

PreparedStatement ps = conn.prepareStatement(sqlString.toString());
ResultSet rs = null;
...
Now when I run the java class, the correct results are shown:
ID   CBT(local time)                     CBT_UTC(GMT)                        Time Difference
1    Sun Apr 05 01:00:00 EST 2009        Sat Apr 04 14:00:00 EST 2009        11
2    Sun Apr 05 02:00:00 EST 2009        Sat Apr 04 15:00:00 EST 2009        11
3    Sun Apr 05 02:00:00 EST 2009        Sat Apr 04 16:00:00 EST 2009        11
4    Sun Apr 05 02:00:00 EST 2009        Sat Apr 04 17:00:00 EST 2009        10
5    Sun Apr 05 03:00:00 EST 2009        Sat Apr 04 18:00:00 EST 2009        10
6    Sun Apr 05 04:00:00 EST 2009        Sat Apr 04 19:00:00 EST 2009        10
7    Sun Apr 05 05:00:00 EST 2009        Sat Apr 04 20:00:00 EST 2009        10
8    Sun Apr 05 06:00:00 EST 2009        Sat Apr 04 21:00:00 EST 2009        10
9    Sun Apr 05 07:00:00 EST 2009        Sat Apr 04 22:00:00 EST 2009        10
10    Sun Apr 05 08:00:00 EST 2009        Sat Apr 04 23:00:00 EST 2009        10
11    Sun Apr 05 09:00:00 EST 2009        Sun Apr 05 00:00:00 EST 2009        10
12    Sun Apr 05 10:00:00 EST 2009        Sun Apr 05 01:00:00 EST 2009        10
13    Sun Apr 05 11:00:00 EST 2009        Sun Apr 05 02:00:00 EST 2009        10
14    Sun Apr 05 12:00:00 EST 2009        Sun Apr 05 02:00:00 EST 2009        10
15    Sun Apr 05 13:00:00 EST 2009        Sun Apr 05 03:00:00 EST 2009        10
16    Sun Apr 05 14:00:00 EST 2009        Sun Apr 05 04:00:00 EST 2009        10
17    Sun Apr 05 15:00:00 EST 2009        Sun Apr 05 05:00:00 EST 2009        10
18    Sun Apr 05 16:00:00 EST 2009        Sun Apr 05 06:00:00 EST 2009        10
19    Sun Apr 05 17:00:00 EST 2009        Sun Apr 05 07:00:00 EST 2009        10

Comments (0)

Post a Comment

Subscribe to: Post Comments (Atom)