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

问题描述

我对SQL DATE数据类型与 java.sql.Date 的行为感到困惑。例如：

  select cast（？as date） - 在大多数情况下数据库
从双重选择cast（？as date）在Oracle 
  

让我们准备和使用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（）; 
 
 //我住在苏黎世，这是CET，而不是GMT。所以以下打印-3600000，
 //是CET 1970-01-01 00:00:00 
 // ...或GMT 1969-12-31 23:00:00 
 System.out.println（rs.getDate（1）.getTime（））; 
  

换句话说，我绑定到语句的GMT时间戳成为CET时间戳。

注意：

• p>我已经观察到这对于任何这些数据库都是如此：
  
  
  

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



• 我已经观察到这是SQLite的错误（实际上并不真实 DATE 数据类型）


• 当使用 java.sql.Timestamp 而不是 java时，所有这些都是无关紧要的。 sql.Date


• 然而，这是一个类似的问题，但这并不回答这个问题： java.util.Date vs java.sql.Date

解决方案

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

最终，日期/时间数据库类型的时区的处理归结为数据库服务器，JDBC驱动程序以及之间的所有内容。你甚至受到JDBC驱动程序错误的怜悯; PostgreSQL修复了版本8.3中的错误，其中

传递Calendar对象的Statement.getTime，.getDate和.getTimestamp方法正在以错误的方向旋转时区。

当您使用新建日期（0）（让我们断言您使用的是Oracle JavaSE java.sql.Date ，创建日期

使用给定的毫秒时间值，如果给定的毫秒值包含时间信息，驱动程序将设置时间组件到默认时区（运行应用程序的Java虚拟机的时区），与GMT相对应。

所以，新的日期（0）应该使用GMT。

Wh您可以致电 ResultSet.getDate（int） ，您正在执行JDBC实现。 JDBC规范不规定JDBC实现应如何处理时区详细信息;所以你受到实施的怜悯。查看Oracle 11g oracle.sql.DATE JavaDoc，它似乎没有Oracle数据库存储时区信息，所以它执行自己的转换，以获取日期到一个 java。 sql.Date 。我没有Oracle DB的经验，但我猜测JDBC实现是使用服务器和本地JVM的时区设置来执行从 oracle.sql.DATE 到 java.sql.Date 。

---

您提到多个RDBMS实现处理时区正确，除了SQLite。我们来看看 H2 和 SQLite 在向JDBC驱动程序发送日期值以及从JDBC驱动程序获取日期值时工作。

H2 JDBC驱动程序< a href =https://code.google.com/p/h2database/source/browse/trunk/h2/src/main/org/h2/jdbc/JdbcPreparedStatement.java#359 =noreferrer> PrepStmt.setDate（int，Date） 使用 ValueDate.get（Date） ，它调用< a href =https://code.google.com/p/h2database/source/browse/trunk/h2/src/main/org/h2/util/DateTimeUtils.java#732 =noreferrer> DateTimeUtils.dateValueFromDate（long） 进行时区转换。

使用这个 SQLite JDBC驱动程序， PrepStmt。 setDate（int，Date） 调用 PrepStmt.setObject（int，Object） ，不进行任何时区转换。 / p>

H2 JDBC驱动程序 JdbcResultSet.getDate（int） 返回 get（columnIndex ）.getDate（）。 get（int） 返回H2 值 。由于列类型为 DATE ，H2使用 ValueDate 。 ValueDate.getDate（） 调用 DateTimeUtils.convertDateValueToDate（long） ，最终创建一个 java.sql.Date 在时区转换后。

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

所以我们看到H2 JDBC驱动程序在处理带有日期的时区转换方面很聪明，而SQLite JDBC驱动程序不是（不要说这个决定不是很聪明，它可能适合SQLite设计决策很好）。如果您追踪您提到的其他RDBMS JDBC驱动程序的源代码，那么您可能会发现大部分都是以类似于H2的方式接近日期和时区。

---

我发现这个Microsoft SQL Server博客在SQL Server 2008中使用时区数据，这说明时区如何使事情变得复杂：

时区是一个复杂的区域，每个应用程序都需要处理如何处理时区数据，使程序更加用户友好。

不幸的是，目前没有时区名称和价值观的现行国际标准机构。每个系统需要使用自己选择的系统，直到有国际标准，尝试让SQL Server提供一个，并且最终会导致比解决问题更多的问题是不可行的。

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

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());

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?

Note:

• 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

• I have observed this to be false for SQLite (which doesn't really have true DATE data types)
• All of this is irrelevant when using java.sql.Timestamp instead of java.sql.Date
• This is a similar question, which doesn't answer this question, however: java.util.Date vs java.sql.Date

解决方案

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!

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

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

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

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.

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

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.

---

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.

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

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

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.

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.

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.

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.

---

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.

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屋！