在 Oracle JDBC 驱动程序中，当您将 Java 日期写入 TIMESTAMP 列时，时区会发生什么变化？

In the Oracle JDBC driver, what happens to the time zone when you write a Java date to a TIMESTAMP column?

提问by auspicacious

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

我四处搜索，但令人惊讶的是找不到 Oracle JDBC 的答案。这个密切相关的问题有 PostgreSQL 和 MySQL 的答案。

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

基本上，如果我在两个不同时区有两个应用程序服务器将时间戳写入一个 Oracle 数据库，会发生什么？谢谢。

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.

编辑：我应该补充一点，当我进行查询时，JDBC 发送到数据库的值似乎在我的本地时区。

采纳答案by auspicacious

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.

我整理了一些测试 JDBC 代码来弄清楚到底发生了什么。结果很有趣。Oracle有三个密切相关的数据类型：TIMESTAMP，TIMESTAMP WITH TIME ZONE，和TIMESTAMP WITH LOCAL TIME ZONE。我采用了完全相同的代码，并从两个不同的框中运行它，一个在“America/New_York”时区，另一个在 UTC 上运行。两者都访问了以 UTC 运行的同一个数据库。我使用的是 Oracle 11.2.0.2.0 驱动程序。

• The TIMESTAMPcolumn 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 ZONEcolumn translated the time to whatever timezone the JDBC client was in.
• The TIMESTAMP WITH LOCAL TIME ZONEcolumn also translated the time to whatever timezone the JDBC client was in.

• 该TIMESTAMP列被设置为执行 Java 代码的机器上的本地时间。没有执行时区转换。
• 该TIMESTAMP WITH TIME ZONE列将时间转换为 JDBC 客户端所在的任何时区。
• 该TIMESTAMP WITH LOCAL TIME ZONE列还将时间转换为 JDBC 客户端所在的任何时区。

This article, which is a bit older, indicates that TIMESTAMP WITH TIME ZONEis pretty much useless if you want to do anything like indexes or partitions. However, it seems like TIMESTAMP WITH LOCAL TIME ZONEmight 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.

这篇文章有点旧，它表明TIMESTAMP WITH TIME ZONE如果你想做索引或分区之类的事情，那几乎没有用。但是，它似乎TIMESTAMP WITH LOCAL TIME ZONE可能非常有用。（不确定如果更改服务器的时区会发生什么，但它似乎对 JDBC 客户端的本地时区很智能）。我还没有机会用这些数据类型测试索引行为等。

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
            }
        }
    }
}