12

I've searched around, and surprisingly can't find an answer to this for Oracle JDBC. This closely related question has answers for PostgreSQL and MySQL.

Basically, if I have two application servers in two different time zones writing timestamps to one Oracle database, what will happen? Thanks.

Edit: I should add that it seems like the value that JDBC is sending to the database when I do queries is in my local time zone.

Community
  • 1
  • 1
auspicacious
  • 573
  • 1
  • 6
  • 18
  • Why are you doing that in the front-end when the database could do it centrally? – Tim Aug 17 '12 at 15:37
  • I'm trying to understand what the JDBC driver does. It looks like, in my logs, it's sending a string representing the local time to Oracle. This bothers me. – auspicacious Aug 17 '12 at 16:20
  • [this doc](http://docs.oracle.com/javase/1.3/docs/guide/jdbc/spec2/jdbc2.1.frame10.html) suggests that what is sent by the JDBC driver isn't a normalized (ie UTC) timestamp but a local timezone timestamp (what I would call random) and the server is supposed to compute its date using the timezone of the server having the driver. This is in accord with your finding. And yes this is bothering. – Denys Séguret Aug 17 '12 at 16:32
  • 1
    I should add that this creates interesting ambiguities when all of your servers are running on UTC and your dev boxes are on local time. – auspicacious Aug 17 '12 at 20:29

1 Answers1

9

I put together some test JDBC code to figure out exactly what happens. The results were interesting. Oracle has three closely related datatypes: TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE. I took the exact same code, and ran it from two different boxes, one in the "America/New_York" timezone, and one running on UTC. Both hit the same database, running in UTC. I was using the Oracle 11.2.0.2.0 driver.

  • The TIMESTAMP column was set to whatever the local time was on the machine executing the Java code. No time zone translation was performed.
  • The TIMESTAMP WITH TIME ZONE column translated the time to whatever timezone the JDBC client was in.
  • The TIMESTAMP WITH LOCAL TIME ZONE column also translated the time to whatever timezone the JDBC client was in.

This article, which is a bit older, indicates that TIMESTAMP WITH TIME ZONE is pretty much useless if you want to do anything like indexes or partitions. However, it seems like TIMESTAMP WITH LOCAL TIME ZONE might be extremely useful. (Not sure what happens if you change the server's time zone, but it seems to be intelligent about JDBC clients' local time zones). I haven't had a chance to test indexing behavior, etc. with these datatypes.

Pasting in my sample class below if you'd like to reproduce my tests in your environment.

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

// create table x_tst_ts_tab(
// os_name varchar(256)
// ts timestamp,
// ts_with_tz timestamp with time zone,
// ts_with_local_tz timestamp with local time zone
// )
class TSTest {
    public static final void main(String[] argv) throws Exception {
        Class.forName("oracle.jdbc.OracleDriver");
        Connection conn = DriverManager.getConnection(
            "your_connection_string",
            "your_user_name",
            "your_password");

        try {
            // Insert some data
            Date nowDate = new Date();
            Timestamp nowTimestamp = new Timestamp(nowDate.getTime());
            PreparedStatement insertStmt = conn.prepareStatement(
                "INSERT INTO x_tst_ts_tab"
                + " (os_name, ts, ts_with_tz, ts_with_local_tz)"
                + " VALUES (?, ?, ?, ?)");
            try {
                insertStmt.setString(1, System.getProperty("os.name"));
                insertStmt.setTimestamp(2, nowTimestamp);
                insertStmt.setTimestamp(3, nowTimestamp);
                insertStmt.setTimestamp(4, nowTimestamp);
                insertStmt.executeUpdate();
            } finally {
                try {
                    insertStmt.close();
                } catch (Throwable t) {
                    // do nothing
                }
            }

            System.out.println("os_name, ts, ts_with_tz, ts_with_local_tz");

            // Read back everything in the DB
            PreparedStatement selectStmt = conn.prepareStatement(
                "SELECT os_name, ts, ts_with_tz, ts_with_local_tz"
                + " FROM dom_fraud_beacon.x_tst_ts_tab");
            ResultSet result = null;
            try {
                result = selectStmt.executeQuery();
                while (result.next()) {
                    System.out.println(
                        String.format("%s,%s,%s,%s",
                                      result.getString(1),
                                      result.getTimestamp(2).toString(),
                                      result.getTimestamp(3).toString(),
                                      result.getTimestamp(4).toString()
                                      ));
                }
            } finally {
                try {
                    result.close();
                } catch (Throwable t) {
                    // do nothing
                } finally {
                    try {
                        selectStmt.close();
                    } catch (Throwable t) {
                        // do nothing
                    }
                }
            }
        } finally {
            try {
                conn.close();
            } catch (Throwable t) {
                // do nothing
            }
        }
    }
}
auspicacious
  • 573
  • 1
  • 6
  • 18