SQL服务器和Java之间的时间戳差异

Question

SQL服务器和Java之间的时间戳差异

4

我需要将一个简单的Java代码中的流程复制到SQL Server存储过程中。它将用于生产环境中的SQL Azure数据库，但我正在对其进行本地SQL Express 12安装的测试。

这个存储过程的一部分是将一些值连接成一个字符串。

以下是我的示例Java代码：

import java.sql.Timestamp;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;

import com.google.common.base.Strings;

public static String concat() {
  //init variables with sample data
  DateFormat dateFormat = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss.SSS");
  Timestamp date = new Timestamp(dateFormat.parse("04/04/2014 21:07:13.897").getTime());

  //format variables into 0-filled strings
  String formattedDate = String.format("%011d", date.getTime() / 1000);

  //concat those strings
  String finalString = ... + formattedDate + ...;
  return finalString;    
}

变量：

| date                    | formatted_date |
| ----------------------- | -------------- |
| 2014-04-04 21:07:13.897 | 01396638433    |

这是SQL的等效写法：

DECLARE @date DATETIME;
DECLARE @formatted_date CHAR(11);
DECLARE @final_string CHAR(22);

--init variables with same data as Java code
SET @date = '2014/04/04 21:07:13.897';

--format variables into 0-filled strings
SET @formatted_date = FORMAT(DATEDIFF(s,'1970-01-01 00:00:00', @date), '00000000000');

--concat those strings
SET @final_string = CONCAT(..., @formatted_date, ...);

变量：

| date                    | formatted_date |
| ----------------------- | -------------- |
| 2014-04-04 21:07:13.897 | 01396645633    |

在检查输出是否相同时，我注意到日期不一样：

Java output:  01396638433
MSSQL output: 01396645633

我打开了这个网站，以了解这个差别的含义：

Java:  GMT: Fri, 04 Apr 2014 19:07:13 GMT, Your time zone: 4/4/2014 21:07:13 GMT+2
MSSQL: GMT: Fri, 04 Apr 2014 21:07:13 GMT, Your time zone: 4/4/2014 23:07:13 GMT+2

时间差正好两小时。

我找到了一条在SQL Server上运行的查询，用于检查时区设置：

DECLARE @TZ SMALLINT
SELECT @TZ=DATEPART(TZ, SYSDATETIMEOFFSET())

DECLARE @TimeZone VARCHAR(50)
EXEC MASTER.dbo.xp_regread 'HKEY_LOCAL_MACHINE',
'SYSTEM\CurrentControlSet\Control\TimeZoneInformation',
'TimeZoneKeyName',@TimeZone OUT

SELECT @TimeZone, CAST(@TZ/60 AS VARCHAR(5))+':'+Cast(ABS(@TZ)%60 AS VARCHAR(5));

输出：

| Time zone               | Offset  |
| ----------------------- | ------- |
| W. Europe Standard Time | 2:0     |

我这样检查了JVM的时区:

Calendar now = Calendar.getInstance();
System.out.println(now.getTimeZone());
System.out.println(System.getProperties().get("user.timezone").toString());

输出：

sun.util.calendar.ZoneInfo[id="Europe/Berlin",offset=3600000, dstSavings=3600000,
transitions=143, lastRule=java.util.SimpleTimeZone[id=Europe/Berlin, offset=3600000, 
dstSavings=3600000, startYear=0, startMode=2, startMonth=2, startDay=-1,
startDayOfWeek=1, startTime=3600000, startTimeMode=2, endMode=2, endMonth=9,
endDay=-1, endDayOfWeek=1, endTime=3600000, endTimeMode=2]]
Europe/Berlin

如何在Java和SQL Server之间获得相等的时间戳？

- Andrea

JDBC中的时间戳需要使用本地时区，请参见https://dev59.com/CmYq5IYBdhLWcg3w8VG8#14070771。 - Mark Rotteveel

3个回答

2

JDBC要求在没有任何时区信息的情况下，使用本地时区存储和检索时间戳。这意味着如果您的本地系统是欧洲/柏林，则在数据库中存储为“2014-04-04 21:07:13.897”的日期将被处理为“2014-04-04 21:07:13.897 CEST”（中欧夏令时），而不是“2014-04-04 21:07:13.897 UTC”。CEST偏移量比UTC提前2小时或7200秒，这就解释了您观察到的差异。

1396645633 - 1396645633 = 7200

当Java将时间戳存储到数据库中时，类似地，它会将时间戳发送为当前时区。因此，如果您尝试存储2014-04-04 21:07:13.897 UTC，则它会使用2014-04-04 23:07:13.897 CEST并发送到SQL Server 2014-04-04 23:07:13.897。

虽然对于setTimestamp(int parameterIndex, Timestamp x)没有明确规定，但驱动程序必须遵循setTimestamp(int parameterIndex, Timestamp x, Calendar cal) javadoc所建立的规则：

将指定的参数设置为给定的java.sql.Timestamp值，使用给定的Calendar对象。驱动程序使用Calendar对象构建一个SQL TIMESTAMP值，然后将其发送到数据库中。使用Calendar对象，驱动程序可以计算时间戳，考虑到自定义时区。如果未指定Calendar对象，则驱动程序使用默认时区，即运行应用程序的虚拟机的时区。

更多详细信息请参见：java.sql.Timestamp是否特定于时区？

- Mark Rotteveel

谢谢您的清晰回答。我不太清楚如何更改我的Java代码以使其正确。我目前使用new Timestamp(System.currentTimeMillis())生成时间戳，然后将该值发送到数据库。稍后有一个预定的SQL存储过程读取该值并进行一些计算。您有什么建议可以更改吗？ - Andrea

如果您想始终使用特定的时区，则使用接受Calendar对象的setter（和getter）方法，并使用具有正确时区的Calendar。如果您使用Java 8并且您的驱动程序已经实现了JDBC 4.2中指定的新的java.time支持，您还可以考虑使用java.time.LocalDateTime类与set/getObject一起使用（但我不确定有多少驱动程序已经完全支持此功能）。 - Mark Rotteveel

我希望不必输入机器时区，因为如果我将服务器移动到另一个地区，这会导致转换问题。是否有一种方法始终将时间戳保存/读取为UTC？ - Andrea

使用带有UTC时区设置的Calendar来设置或检索时间戳，或者如果您的驱动程序支持，则使用LocalDateTime对象。 - Mark Rotteveel

1

尽管Mark Rotteveel和dean给出了启发性的答案，但我最终做了以下事情：

在我的应用程序初始化方法的开头，我设置了：

TimeZone.setDefault(TimeZone.getTimeZone("UTC"));

和所有的SQL调用

getdate()

已经被替换为

getutcdate()

谢谢您的时间！

- Andrea

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- dean · Accepted Answer

在SQL Server方面 --

有一个名为getutcdate()的函数，它返回当前的UTC时间。通过与getdate()进行比较，您可以得到与UTC的时间差，并将该值修改为所需格式。

select datediff(s, getutcdate(), getdate())

优于在任何情况下访问注册表。因此，SQL Server代码应该如下所示：

DECLARE @date DATETIME;
DECLARE @formatted_date CHAR(11);
DECLARE @final_string CHAR(22);
DECLARE @diff_sec int;

--init variables with same data as Java code
SET @date = '2014/04/04 21:07:13.897';

--get the difference between UTC and local in seconds
SET @diff_sec = datediff(s, getutcdate(), getdate());

--format variables into 0-filled strings
SET @formatted_date = FORMAT(DATEDIFF(s,'1970-01-01 00:00:00', @date) - @diff_sec, '00000000000');

--concat those strings
SET @final_string = CONCAT(..., @formatted_date, ...);