Oracle and JDBC test from different timezones

I predicted that Java would send the right thing to Oracle and we'd see the same thing in the database. I was wrong and Vijay was right: Oracle ends up with a different value, even though we send the exact same java.util.Date value.

Following is our test where we point Java programs running in different timezones to the same Oracle database.

Here are our two runs of the program below, from UT and then LA timezones:

$ java -cp .:ojdbc-14_g.jar DateTest UT
UT     Pi Time Los_Angeles: 1205533140000 = Fri Mar 14 16:19:00 MDT 2008
UT SQL Pi Time Los_Angeles: 1205533140000 = 2008-03-14 16:19:00.0

$ java -cp .:ojdbc-14_g.jar DateTest LA
LA     Pi Time Los_Angeles: 1205533140000 = Fri Mar 14 15:19:00 PDT 2008
LA SQL Pi Time Los_Angeles: 1205533140000 = 2008-03-14 15:19:00.0

Here are the results we find in the database when the dust settles:

UT piTimeLos_Angeles 2008-03-14 16:19:00.0
LA piTimeLos_Angeles 2008-03-14 15:19:00.0


DateTest.java:

import java.util.*;
import java.sql.*;
import java.text.*;


/**

create table time_test (
name varchar2(100),
time date
)

*/
public class DateTest {
public static void main(String[] args) {
  String outPrefix = "";
  if (args.length > 0) {
    outPrefix = args[0];
  }

  Connection conn = null;
  PreparedStatement pstat = null;
  try {
    SimpleDateFormat formatter = new SimpleDateFormat("yyyy/MM/dd HH:mm");
    TimeZone timezone = TimeZone.getTimeZone("America/Los_Angeles");
    formatter.setTimeZone(timezone);
    java.util.Date piTimeLos_Angeles = formatter.parse("2008/03/14 15:19");
    System.out.println(outPrefix + " Pi Time Los_Angeles: " + piTimeLos_Angeles.getTime() + " = " + piTimeLos_Angeles);

    String DRIVER = "oracle.jdbc.driver.OracleDriver";
    String URL = "...";
    String USERNAME = "...";
    String PASSWORD = "...";
    Class.forName(DRIVER);

    conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
    pstat = conn.prepareStatement("insert into time_test (name, time) values (?, ?)");

    //java.sql.Date piTimeLos_AngelesSql = new java.sql.Date(piTimeLos_Angeles.getTime());
    java.sql.Timestamp piTimeLos_AngelesSql = new java.sql.Timestamp(piTimeLos_Angeles.getTime());
    System.out.println(outPrefix + " SQL Pi Time Los_Angeles: " + piTimeLos_AngelesSql.getTime() + " = " + piTimeLos_AngelesSql);

    pstat.setString(1, outPrefix + " piTimeLos_Angeles");
    //pstat.setDate(2, piTimeLos_AngelesSql);
    pstat.setTimestamp(2, piTimeLos_AngelesSql);
    pstat.executeUpdate();

    pstat.close();
    conn.close();

  } catch (Exception e) {
    e.printStackTrace();
  }
}
}