用于UTC或带有时区的时间戳的SQL标准 [英] SQL standard for timestamp in UTC or with timezone
问题描述
我正在寻找一种标准的SQL方法，以确保独立于数据库而具有UTC时间戳记或时区信息时间戳记的列。在数据库列中存储时间戳UTC的
UTC示例：
I am searching for an standard SQL way to ensure independently from the database to have a column with a timestamp in UTC or as timestamp with the information of the timezone. UTC example with stored timestamp UTC in DB-column:
-
存储的UTC值= 2014-01 -01 15：30：00.000
印度当地时间印度当地时间21：00 / 9pm作为 2014-01-01 21：00：00.000
local time India 21:00/9pm as local timestamp "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
local time India 05:00/5am as local timestamp "2014-01-01 05:00:00.000"
采用UTC方式，应用程序必须应对时区
现在我不知道是否可以具有上述值的时间戳记和时区
And now the way I do not really know if possible, with timestamp and timezone with the above values
-
存储的本地值= 2014-01-01 21：00：00.000
印度当地时间21：00 / 9pm作为当地时间戳 2014-01-01 21：00：00.000
local time India 21:00/9pm as local timestamp "2014-01-01 21:00:00.000"
但是有时区信息吗？
还是如何获取此时间戳的时区信息？
or how to get the timezone information of this timestamp?
存储的本地值= 2014-01-01 05：00：00.000
印度当地时间05：00 / 5am作为当地时间戳 2014-01-01 05 ：00：00.000
local time India 05:00/5am as local timestamp "2014-01-01 05:00:00.000"
，但是有时区信息吗？
或如何获取以下时区信息这个时间戳记？
or how to get the timezone information of this timestamp?
希望有人可以帮助我摆脱困境吗？
在时区问题和存储数据以供国际应用方面是否有好的做法？
Hopefully someone can help me out of the dark? Are there good practices on timezoning issues and storing the data for international application?
推荐答案
tl; dr
要存储时间，即时间线上的一点，请定义SQL标准类型 TIMESTAMP WITH TIME ZONE 的数据库列。
在UTC中存储时间。
Instant instant = Instant.now() ; // Capture the current moment in UTC.
myPreparedStatement.setObject( … , instant ) ;
在UTC中获取时间。
Retrieve a moment in UTC.
Instant instant = myResultSet.getObject( … , Instant.class ) ;
从UTC调整到时区。相同的时刻，不同的时钟时间。
Adjust from UTC to a time zone. Same moment, different wall-clock time.
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。相同的时刻，不同的时钟时间。
Adjust from a time zone to UTC. Same moment, different wall-clock time.
Instant instant = zdt.toInstant() ;
日期时间不是字符串
严重的数据库不会将日期时间值存储为字符串。不要将数据库或Java库生成的字符串表示形式与日期时间值本身混淆。
Date-Time Is Not A String
Serious databases do not store date-time values as strings. Do not confuse a string representation generated by either the database or a Java library with the date-time value itself.
最佳做法是为您使用 UTC 数据库和其他存储，以及大部分业务逻辑。仅在用户期望的情况下出现在本地时区中。
Best practice is to use UTC for your database and other storage, as well as the bulk of your business logic. Present in local time zones only as expected by the user.
有关标准日期时间类型，请参见Wikipedia。即使您不使用Postgres，也请在日期时间查阅出色的文档数据类型。但是我确实推荐Postgres，原因有很多，包括对日期时间的出色支持。
See Wikipedia for standard date-time types. Even if you do not use Postgres, consult the outstanding documentation on date-time data types. But I do recommend Postgres, for many reasons including its excellent support of date-time.
这位Postgres专家提出了简单而明智的建议：始终在时区使用TIMESTAMP
This Postgres expert makes the simple and wise recommendation: Always Use TIMESTAMP WITH TIME ZONE
该数据类型的名称为不当使用，造成了很多混乱。 不存储时区信息。这意味着将尊重传入数据指示的时区，并且存储的值将被调整为 UTC 。将带有时区的时间戳视为带有时区的时间戳。重读此段落三遍，然后阅读上面的链接，并做一些实验以确保您理解。
The name of that data type is a misnomer, causing so much confusion. The time zone information is not stored. What it means is that the time zone indicated with incoming data is respected, and the stored value will be adjusted to UTC. Think of TIMESTAMP WITH TIME ZONE as TIMESTAMP WITH RESPECT FOR TIME ZONE. Reread this paragraph three times out loud, then read the above links, and do some experimenting to be sure you understand.
您可能还希望单独存储原始时区信息。
You may want to also store separately the original time zone information. Not for use in your business logic, but for use as logging info for debugging.
不用于您的业务逻辑，而用作调试的日志信息。 Java，请确保避免使用java.util.Date和.Calendar类。他们出了名的麻烦。新的
As for Java, be sure to avoid the java.util.Date and .Calendar classes. They are notoriously troublesome. They are supplanted in Java 8 by the new java.time package. Use either that package and/or the Joda-Time 2.4 library which inspired java.time.
在Java中，始终指定所需的时区。如果省略，您将隐式使用JVM的当前默认时区。隐式默认表示您的结果会随时间和空间而变化。这是日期时间工作中许多麻烦的根本原因。如果要使用UTC，请在Joda-Time中使用常量， DateTimeZone.UTC 。
And in Java always specify the desired time zone. If omitted you'll implicitly be using the JVM's current default time zone. That implicit default means your results will vary across time and space. This is the root cause of much of the trouble in date-time work. If you want UTC, use the constant in Joda-Time, DateTimeZone.UTC.
忽略时区不会使您的生活更轻松。
Ignoring time zones will not make your life easier.
此标准非常有用且明智。研究出色的Wikipedia页面。
This standard is extremely useful and sensible. Study the excellent Wikipedia page. Should be your first choice for a String representation.
这些问题已经涉及数百种了，如果不是成千上万的答案。
These issues have been covered on hundreds, if not thousands, of answers.
这篇关于用于UTC或带有时区的时间戳的SQL标准的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
