在SQL Server中使用UTC时间保存当前日期的问题 [英] Issue in saving current date with UTC time in sql server
问题描述
我想在SQL Server 2014中使用UTC时区保存日期.我已经使用ZonedDateTime zonedDateTime = ZonedDateTime.now(ZoneId.of("UTC"))来获取当前日期时间，并使用Hibernate会话对象将其持久化到数据库.在调试时，我可以看到在Java程序中日期像这样2019-09-25T13:22:29.573Z[UTC]很好，但是在数据库列中保存之后，日期就像这样2019-09-25 18:53:23.3630000.它会根据系统时间自动转换时间部分.有人可以建议出什么问题吗?在数据库中创建此列时，我使用了datetime2数据类型.
I want save date with UTC time zone in sql server 2014. I have used ZonedDateTime zonedDateTime = ZonedDateTime.now(ZoneId.of("UTC")) to get the current date time and persisting this to db using Hibernate session object. While debugging I can see that in Java program date is fine like this 2019-09-25T13:22:29.573Z[UTC], but after saving in database column it is something like this 2019-09-25 18:53:23.3630000. It's automatically converting the time part according to system time. Can anyone please suggest what is the issue? I have used datetime2 datatype while creating this column in database.
推荐答案
类型错误
您为列使用了错误的数据类型.
Wrong type
You are using the wrong data type for your column.
类型 datetime2不能表示时刻.该类型仅存储日期和时间，但缺少时区或UTC偏移量的上下文.因此，如果您存储"2020年1月23日中午"，我们不知道您是指日本东京中午，突尼斯突尼斯中午还是美国俄亥俄州托莱多中午，相隔三个小时.此错误类型类似于SQL标准类型TIMESTAMP WITHOUT TIME ZONE. Java中的等效类为
The type datetime2 cannot represent a moment. That type stores only a date and a time-of-day, but lacks the context of a time zone or offset-from-UTC. So if you store "noon on the 23rd of January in 2020", we cannot know if you meant noon in Tokyo Japan, noon in Tunis Tunisia, or noon in Toledo Ohio US, three different moments several hours apart. This wrong type is akin to the SQL standard type TIMESTAMP WITHOUT TIME ZONE. The equivalent class in Java is LocalDateTime.
如果您已经存储了数据，则需要重构数据库.基本上，添加一个正确类型的新列，为每一行复制数据，然后进行转换.当然，仅当您知道每个保存的值都不存在预期的时区时，此方法才有效.
If you already have data stored, you will need to refactor your database. Basically, add a new column of the correct type, copy data over for each row, converting as you go. Of course, this only works if you know the intended time zone that was absent from each value saved.
虽然我不使用Microsoft SQL Server，但根据文档，您应该使用类型为 datetimeoffset的列来记录时刻.此类型将所有提交的值调整为UTC以进行查询和排序，同时还记录偏移量.此类型类似于SQL标准类型TIMESTAMP WITH TIME ZONE.
While I don’t use Microsoft SQL Server, according to the doc you should be using a column of type datetimeoffset to record a moment. This type adjusts any submitted value into UTC for queries and sorting, while also recording the offset. This type is akin to the SQL standard type TIMESTAMP WITH TIME ZONE.
通常最好将您的时刻存储在UTC中，零时分-秒-秒.
Generally best to store your moments in UTC, an offset of zero hours-minutes-seconds.
Instant类表示UTC中的时刻.
The Instant class represents a moment in UTC.
Instant instant = Instant.now() ;
如果有ZonedDateTime，则可以提取Instant.
If you have a ZonedDateTime, you can extract an Instant.
Instant instant = zdt.toInstant() ;
我们想将Instant直接保存到数据库中.不幸的是，JDBC 4.2 soec不需要支持两个最常用的 java.time 类之一:Instant和ZonedDateTime.该规范确实需要支持OffsetDateTime.因此，我们进行了转换.
We would like to save that Instant directly to the database. Unfortunately the JDBC 4.2 soec does not require support for either of the two most commonly used java.time classes: Instant and ZonedDateTime. The spec does require support for OffsetDateTime. So we convert.
OffsetDateTime odt = instant.atOffset( ZoneOffset.UTC ) ;
提交到数据库.
myPreparedStatement.setObject( … , odt ) ;
检索.
OffsetDateTime odt = MyResultSet.getObject( … , OffsetDateTime.class ) ;
提取一个Instant以在您的代码中清楚您想要的UTC.
Extract an Instant to make clear in your code that you want UTC.
Instant instant = odt.toInstant() ;
通过特定地区(时区)的人们使用的挂钟时间来查看这一刻.
See this moment through the wall-clock-time used by the people of a particular region (a time zone).
ZoneId z = ZoneId.of( "Asia/Kolkata" ) ;
ZonedDateTime zdt = instant.atZone( z ) ;
这篇关于在SQL Server中使用UTC时间保存当前日期的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
