SQL DATE 与 java.sql.Date 中的时区 [英] Timezones in SQL DATE vs java.sql.Date

问题描述

我对 SQL DATE 数据类型的行为与 java.sql.Date 的行为感到有些困惑.以下面的语句为例:

I'm getting a bit confused by the behaviour of the SQL DATE data type vs. that of java.sql.Date. Take the following statement, for example:

select cast(? as date)           -- in most databases
select cast(? as date) from dual -- in Oracle

让我们用Java准备和执行语句

Let's prepare and execute the statement with Java

PreparedStatement stmt = connection.prepareStatement(sql);
stmt.setDate(1, new java.sql.Date(0)); // GMT 1970-01-01 00:00:00
ResultSet rs = stmt.executeQuery();
rs.next();

// I live in Zurich, which is CET, not GMT. So the following prints -3600000, 
// which is CET 1970-01-01 00:00:00
// ... or   GMT 1969-12-31 23:00:00
System.out.println(rs.getDate(1).getTime());

换句话说，我绑定到语句的 GMT 时间戳变成了我返回的 CET 时间戳.在哪一步添加时区，为什么?

In other words, the GMT timestamp I bind to the statement becomes the CET timestamp I get back. At what step is the timezone added and why?

注意:

• 我已经观察到这对于这些数据库中的任何一个都是正确的:

• I have observed this to be true for any of these databases:

DB2、Derby、H2、HSQLDB、Ingres、MySQL、Oracle、Postgres、SQL Server、Sybase ASE、Sybase SQL Anywhere

DB2, Derby, H2, HSQLDB, Ingres, MySQL, Oracle, Postgres, SQL Server, Sybase ASE, Sybase SQL Anywhere

推荐答案

JDBC 规范 没有定义任何关于时区的细节.尽管如此，我们大多数人都知道必须处理 JDBC 时区差异的痛苦；只需查看所有 StackOverflow 问题！

The JDBC specification does not define any details with regards to time zone. Nonetheless, most of us know the pains of having to deal with JDBC time zone discrepencies; just look at all the StackOverflow questions!

最终，日期/时间数据库类型的时区处理归结为数据库服务器、JDBC 驱动程序以及两者之间的一切.您甚至会受到 JDBC 驱动程序错误的影响；PostgreSQL 修复了 8.3 版中的一个错误，其中

Ultimately, the handling of time zone for date/time database types boils down to the database server, the JDBC driver and everything in between. You're even at the mercy of JDBC driver bugs; PostgreSQL fixed a bug in version 8.3 where

通过 Calendar 对象传递的Statement.getTime、.getDate 和.getTimestamp 方法将时区旋转到错误的方向.

Statement.getTime, .getDate, and .getTimestamp methods which are passed a Calendar object were rotating the timezone in the wrong direction.

当您使用 new Date(0) 创建新日期时(假设您使用的是 Oracle JavaSE java.sql.Date，你的日期创建了

When you create a new date using new Date(0) (let's assert you are using Oracle JavaSE java.sql.Date, your date is created

使用给定的毫秒时间值.如果给定的毫秒值包含时间信息，则驱动程序会将时间组件设置为对应于零 GMT 的默认时区(运行应用程序的 Java 虚拟机的时区)中的时间.

using the given milliseconds time value. If the given milliseconds value contains time information, the driver will set the time components to the time in the default time zone (the time zone of the Java virtual machine running the application) that corresponds to zero GMT.

所以，new Date(0) 应该使用 GMT.

So, new Date(0) should be using GMT.

当您调用 ResultSet.getDate(int)，您正在执行 JDBC 实现.JDBC 规范并未规定 JDBC 实现应如何处理时区详细信息；所以你受制于实施.查看 Oracle 11g oracle.sql.DATE JavaDoc，Oracle DB 似乎不存储时区信息，因此它执行自己的转换以将日期转换为 java.sql.Date.我没有使用 Oracle DB 的经验，但我猜想 JDBC 实现是使用服务器和本地 JVM 的时区设置来进行从 oracle.sql.DATE 到 java.sql 的转换.日期.

When you call ResultSet.getDate(int), you're executing a JDBC implementation. The JDBC specification does not dictate how a JDBC implementation should handle time zone details; so you're at the mercy of the implementation. Looking at the Oracle 11g oracle.sql.DATE JavaDoc, it doesn't seem Oracle DB stores time zone information, so it performs its own conversions to get the date into a java.sql.Date. I have no experience with Oracle DB, but I would guess the JDBC implementation is using the server's and your local JVM's time zone settings to do the conversion from oracle.sql.DATE to java.sql.Date.

您提到多个 RDBMS 实现正确处理时区，SQLite 除外.让我们看看 H2 和 SQLite 在您将日期值发送到 JDBC 驱动程序以及从 JDBC 驱动程序获取日期值时起作用.

You mention that multiple RDBMS implementations handle time zone correctly, with the exception of SQLite. Let's look at how H2 and SQLite work when you send date values to the JDBC driver and when you get date values from the JDBC driver.

H2 JDBC 驱动程序 PrepStmt.setDate(int, Date) 使用 ValueDate.get(Date)，它调用了DateTimeUtils.dateValueFromDate(long) 进行时区转换.

The H2 JDBC driver PrepStmt.setDate(int, Date) uses ValueDate.get(Date), which calls DateTimeUtils.dateValueFromDate(long) which does a time zone conversion.

使用这个 SQLite JDBC 驱动程序，PrepStmt.setDate(int, Date) 调用 PrepStmt.setObject(int, Object) 并且不做任何时区转换.

Using this SQLite JDBC driver, PrepStmt.setDate(int, Date) calls PrepStmt.setObject(int, Object) and does not do any time zone conversion.

H2 JDBC 驱动程序 JdbcResultSet.getDate(int) 返回 get(columnIndex).getDate().get(int) 返回一个 H2 Value 用于指定的列.由于列类型是 DATE，H2 使用 ValueDate.ValueDate.getDate() 调用 DateTimeUtils.convertDateValueToDate(long)，最终创建一个 java.sql.Date时区转换.

The H2 JDBC driver JdbcResultSet.getDate(int) returns get(columnIndex).getDate(). get(int) returns an H2 Value for the specified column. Since the column type is DATE, H2 uses ValueDate. ValueDate.getDate() calls DateTimeUtils.convertDateValueToDate(long), which ultimately creates a java.sql.Date after a time zone conversion.

使用这个 SQLite JDBC 驱动程序，RS.getDate(int) 代码要简单得多；它只是使用存储在数据库中的 long 日期值返回一个 java.sql.Date.

Using this SQLite JDBC driver, the RS.getDate(int) code is much simpler; it just returns a java.sql.Date using the long date value stored in the database.

所以我们看到 H2 JDBC 驱动程序在处理带有日期的时区转换方面很聪明，而 SQLite JDBC 驱动程序则不然(并不是说这个决定不聪明，它可能很适合 SQLite 设计决策).如果您查找您提到的其他 RDBMS JDBC 驱动程序的源代码，您可能会发现大多数都以与 H2 类似的方式接近日期和时区.

So we see that the H2 JDBC driver is being smart about handling time zone conversions with dates while the SQLite JDBC driver is not (not to say this decision isn't smart, it might suit SQLite design decisions well). If you chase down the source for the other RDBMS JDBC drivers you mention, you will probably find that most are approaching date and time zone in a similar fashion as how H2 does.

虽然 JDBC 规范没有详细说明时区处理，但 RDBMS 和 JDBC 实现设计者考虑时区并正确处理它是有道理的；特别是如果他们希望他们的产品在全球范围内有销路的话.这些设计师非常聪明，即使在没有具体规范的情况下，他们中的大多数人都能做到这一点，我并不感到惊讶.

Though the JDBC specifications do not detail time zone handling, it makes good sense that RDBMS and JDBC implementation designers took time zone into consideration and will handle it properly; especially if they want their products to be marketable in the global arena. These designers are pretty darn smart and I am not surprised that most of them get this right, even in the absence of a concrete specification.

我发现了这个 Microsoft SQL Server 博客，在 SQL Server 2008 中使用时区数据，解释了时区如何使事情复杂化:

I found this Microsoft SQL Server blog, Using time zone data in SQL Server 2008, which explains how time zone complicates things:

时区是一个复杂的领域，每个应用程序都需要解决您将如何处理时区数据以使程序更加用户友好.

timezones are a complex area and each application will need to address how you are going to handle time zone data to make programs more user friendly.

不幸的是，目前没有关于时区名称和值的国际标准权威.每个系统都需要使用自己选择的系统，在没有国际标准之前，试图让 SQL Server 提供一个是不可行的，最终会导致更多的问题.

Unfortunately, there is no current international standard authority for timezone names and values. Each system needs to use a system of their own choosing, and until there is an international standard, it is not feasible to try to have SQL Server provide one, and would ultimately cause more problems than it would solve.

这篇关于SQL DATE 与 java.sql.Date 中的时区的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！