UTC或带时区的时间戳的SQL标准

3
我正在寻找一种标准的SQL方法,以确保无论数据库如何,都可以拥有一个带有UTC时间戳或带有时区信息的时间戳列。
UTC示例,存储UTC在DB列中的时间戳:
  • 存储UTC值 = 2014-01-01 15:30:00.000

    印度当地时间21:00/9pm作为本地时间戳"2014-01-01 21:00:00.000"

  • 存储UTC值 = 2013-12-31 23:30:00.000

    印度当地时间05:00/5am作为本地时间戳"2014-01-01 05:00:00.000"

按照UTC方式,应用程序必须处理时区问题

现在是我不太确定是否可能使用上述值进行时间戳和时区的方式:

  • 存储本地值 = 2014-01-01 21:00:00.000

    印度当地时间21:00/9pm作为本地时间戳"2014-01-01 21:00:00.000"

    但是有时区信息吗?

    还是如何获取此时间戳的时区信息?

  • 存储本地值 = 2014-01-01 05:00:00.000

    印度当地时间05:00/5am作为本地时间戳"2014-01-01 05:00:00.000"

    但是有时区信息吗?

    还是如何获取此时间戳的时区信息?

希望有人能帮我解决这个问题?是否有关于时区问题和国际应用程序数据存储的良好实践?


这取决于您的关系型数据库管理系统。不同的系统具有存储不同内容的能力。iSeries上的DB2根本无法存储时区。一些RDBMS不存储历史时区(例如,美国几年前更改了夏令时),这可能是一个问题。另一部分取决于您对信息的处理方式。如果您正在存储日志,则与创建日历不同。您在这里做什么? - Clockwork-Muse
1个回答

8

简述

为了存储时间线上的某一时刻,需要定义一个 SQL 标准类型为 TIMESTAMP WITH TIME ZONE 的数据库列。

将时间点存储为协调世界时(UTC)。

Instant instant = Instant.now() ;  // Capture the current moment in UTC. 
myPreparedStatement.setObject( … , instant ) ;

检索UTC中的一个时刻。

Instant instant = myResultSet.getObject( … , Instant.class ) ; 

将时间从UTC调整为时区时间。同一时刻,不同的挂钟时间。

ZoneId z = ZoneId.of( "Asia/Kolkata" ) ;  // Specify time zone in proper `Continent/Region` name, never the ambiguous non-standard 3-4 letter pseudo-zone such as `IST` or `PST`. 
ZonedDateTime zdt = instant.atZone( z ) ;

将时区调整为UTC。同一时刻,不同的挂钟时间。

Instant instant = zdt.toInstant() ;

日期时间不是字符串

严谨的数据库不会将日期时间值存储为字符串。请不要将由数据库或Java库生成的字符串表示与日期时间值本身混淆。

使用UTC进行存储

最佳实践是在数据库和其他存储中使用UTC,以及大部分业务逻辑。仅根据用户的期望在本地时区中呈现。

请参见维基百科标准日期时间类型。即使您不使用Postgres,请查阅其出色的日期时间数据类型文档。但我推荐使用Postgres,原因有很多,包括其对日期时间的出色支持。

使用TIMESTAMPZ

这位Postgres专家提出了简单而明智的建议:始终使用带有时区的时间戳
该数据类型的名称是一个错误的命名,导致了很多混淆。时区信息没有被存储。它的意思是尊重传入数据所指示的时区,并将存储的值调整为UTC。将TIMESTAMP WITH TIME ZONE视为TIMESTAMP WITH RESPECT FOR TIME ZONE。大声朗读本段三次,然后阅读上面的链接,并进行一些实验以确保您理解。
您可能还想单独存储原始时区信息。不用于业务逻辑,而用作调试日志信息。

Java

关于Java,一定要避免使用java.util.Date和.Calendar类。它们非常麻烦。在Java 8中,它们被新的java.time包所取代。使用该包和/或Joda-Time 2.4库(启发了java.time)。
并且在Java中,一定要指定所需的时区。如果省略,则会隐式地使用JVM的当前默认时区。这个隐含默认值意味着您的结果会因时间和空间而异。这是日期时间工作中许多问题的根本原因。如果想使用UTC,请使用Joda-Time中的常量DateTimeZone.UTC
忽略时区不会使您的生活更轻松。

ISO 8601

这个标准非常有用和合理。请查看优秀的维基百科页面。应该是你表示字符串的首选。

搜索StackOverflow

这些问题已经被数百甚至数千个答案涵盖。


网页内容由stack overflow 提供, 点击上面的
可以查看英文原文,
原文链接