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

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.

我已经四处寻找，但令人惊讶的是无法为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似乎是发送到数据库的值是在我的本地时区。

1 个解决方案

#1

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 TIMESTAMP column was set to whatever the local time was on the machine executing the Java code. No time zone translation was performed.
• TIMESTAMP列设置为执行Java代码的机器上的本地时间。没有执行时区转换。
• The TIMESTAMP WITH TIME ZONE column translated the time to whatever timezone the JDBC client was in.
• TIMESTAMP WITH TIME ZONE列将时间转换为JDBC客户端所在的任何时区。
• The TIMESTAMP WITH LOCAL TIME ZONE column also translated the time to whatever timezone the JDBC client was in.
• TIMESTAMP WITH LOCAL TIME ZONE列还将时间转换为JDBC客户端所在的任何时区。

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.

这篇文章有点旧，表明如果你想做索引或分区之类的事情，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
            }
        }
    }
}