我整理了一些测试JDBC代码以弄清楚到底发生了什么。结果很有趣。Oracle有三个密切相关的数据类型：TIMESTAMP，TIMESTAMP WITHTIME ZONE，和TIMESTAMP WITH LOCAL TIME ZONE。我采用了完全相同的代码，并从两个不同的框中运行它，一个在“ America / New_York”时区，另一个在UTC上运行。两者都命中相同的数据库，并以UTC运行。我正在使用Oracle11.2.0.2.0驱动程序。
这篇文章比较旧，表明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
}
}
}
}
602392714
清零编程群