疯狂MS SQL Server JDBC驱动程序在datetime字段或时区问题？ [英] Crazy MS SQL Server JDBC driver at datetime field or time zone issue?

问题描述

在调试期间，我以简单的例子结束：

During debugging I end with simple example:

select convert(datetime, '2015-04-15 03:30:00') as ts
go

Apr 15 2015 03:30AM
(1 row affected)

select convert(int, datediff(second, '1970-01-01 00:00:00',
                    convert(datetime, '2015-04-15 03:30:00'))) as ts
go

1429068600
(1 row affected)

$ TZ=UTC date --date=@1429068600 +%F_%T
2015-04-15_03:30:00

当我从JDBC执行查询时，我得到两个不同于上面的结果!!!代码：

When I perform query from JDBC I get 2 different results not equal to above!!!! Code:

String TEST_QUERY = "select convert(datetime, '2015-04-15 03:30:00') as ts";
PreparedStatement stmt2 = conn.prepareStatement(TEST_QUERY);
ResultSet rs = stmt2.executeQuery();
rs.next();
logger.info("getTimestamp().getTime(): {}", rs.getTimestamp("ts").getTime());
logger.info("getDate().getTime(): {}", rs.getDate("ts").getTime());
stmt2.close();

执行结果（我用Coreutils双重检查结果 date utility）：

Execution result (I double-check result with Coreutils date utility):

=> getTimestamp().getTime(): 1429057800000

$ TZ=UTC date --date=@1429057800 +%F_%T
2015-04-15_00:30:00

=> getDate().getTime(): 1429045200000

$ TZ=UTC date --date=@1429045200 +%F_%T
2015-04-14_21:00:00

日期类型和JDBC Java映射的官方文档对于导致的差异不说...

Official docs for date types and JDBC Java mapping say nothing about resulted difference...

我的程序在 GMT + 03：00 时区执行，我有SQL Server 2008，并尝试使用JDBC驱动程序4.0和4.1从 https://msdn.microsoft.com/en-us/sqlserver/aa937724.aspx

My program executed in GMT+03:00 timezone and I have SQL Server 2008 and try with JDBC driver 4.0 and 4.1 from https://msdn.microsoft.com/en-us/sqlserver/aa937724.aspx

在所有情况下，我希望得到UTC时间戳（从1970年起），这仅适用于我用于交互式调试查询的Linux ODBC tsql 实用程序

My expectation to get UTC timestamp (from 1970) in all cases, which is true only for Linux ODBC tsql utility which I use to interactively debug queries.

WTF？

推荐答案

您的第一对查询计算当地午夜（当地）时代的秒数。任何时区的区别是相同的，因此当您将数据库时区设置为UTC并将先前确定的偏移量转换回时间戳时，您将获得与数字匹配的相同日期和时间，但它们代表不同的绝对时间，因为它们相对于不同的TZ。

Your first pair of queries compute the number of seconds since local midnight on the (local) date of the epoch. This difference is the same in any time zone, so when you set the database timezone to UTC and convert the previously-determined offset back to a timestamp you get the "same" date and time in the sense that the numbers match, but they represent a different absolute time because they are relative to a different TZ.

当您通过JDBC执行查询时，您计算的是一个 Timestamp 在数据库时区GMT + 03：00。 java.sql.Timestamp 表示绝对时间，但是表示为在时代轮到的午夜 GMT 之间的偏移量。 JDBC驱动程序知道如何补偿。因此，您登录的时间是 1970-01-01 00:00:00 GMT + 00：00 和 2015-04之间的时差-15 03:30:00 GMT + 03：00 。

When you execute your query through JDBC, you are computing a Timestamp in the database timezone, GMT+03:00. java.sql.Timestamp represents absolute time, however, expressed as an offset from midnight GMT at the turn of the epoch. The JDBC driver knows how to compensate. What you then log, therefore, is the time difference between 1970-01-01 00:00:00 GMT+00:00 and 2015-04-15 03:30:00 GMT+03:00.

getDate（）。getTime（） / code>版本有点不太清楚，但是看起来当您将时间戳记检索为 Date ，从而截断时间部分时，截断为相对于数据库时区执行。之后，与其他情况相似， java.sql.Date.getTime（）将从时代的转折返回到所产生的绝对时间的偏移量。也就是说，它正在计算 1970-01-01 00:00:00 GMT + 00：00 和 2015-04-15 00之间的区别：00：00 GMT + 03：00

The getDate().getTime() version is a little less clear cut, but it appears that when you retrieve the timestamp as a Date, thereby truncating the time part, the truncation is being performed relative to the database time zone. Afterward, and similarly to the other case, java.sql.Date.getTime() returns an offset from the turn of the epoch to the resulting absolute time. That is, it is computing the difference between 1970-01-01 00:00:00 GMT+00:00 and 2015-04-15 00:00:00 GMT+03:00

这是一致的。

这篇关于疯狂MS SQL Server JDBC驱动程序在datetime字段或时区问题？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！