jdbc oracle 时间格式,在Oracle JDBC驱动程序中,将Java日期写入TIMESTAMP列时,时区会发生什么？...

我把一些测试JDBC代码放在一起,以确定究竟发生了什么.结果很有趣. Oracle有三种密切相关的数据类型：TIMESTAMP,TIMESTAMP WITH TIME ZONE和TIMESTAMP WITH LOCAL TIME ZONE.我使用完全相同的代码,从两个不同的框中运行它,一个在“America / New_York”时区,一个在UTC上运行.两者都以UTC格式运行相同的数据库.我使用的是Oracle 11.2.0.2.0驱动程序.

> TIMESTAMP列设置为执行Java代码的机器上的本地时间.没有执行时区转换.

> TIMESTAMP WITH TIME ZONE列将时间转换为JDBC客户端所在的任何时区.

> TIMESTAMP WITH LOCAL TIME ZONE列还将时间转换为JDBC客户端所在的任何时区.

This article,它有点旧,表示如果你想做索引或分区之类的事情,TIMESTAMP WITH TIME ZONE几乎没用.但是,似乎TIMESTAMP WITH LOCAL TIME ZONE可能非常有用. (不确定如果更改服务器的时区会发生什么,但它似乎对JDBC客户端的本地时区是智能的).我没有机会使用这些数据类型测试索引行为等.

如果您想在您的环境中重现我的测试,请在下面的示例类中粘贴.

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

}

}

}

}