我如何告诉 postgres 列中的时间戳是 UTC? [英] How do I tell postgres a timestamp within a column is UTC?

问题描述

我们有一个从源获取数据的应用程序，该源以 UTC 时间戳显示数据.当我们的应用程序将该数据保存到 Postgres 时，它会将时间戳存储在一个没有时区的时间戳列中.我们商店中 postgres 的默认设置为我们的当地时间，山地时间.所以这意味着，我认为，postgres 假设时间戳是山区时间.如何查询该列，以便我的结果集认为它是 UTC 而不是本地时区?

更明确地说，我需要对该时间戳执行一些偏移(将其移动到，比如说 EST)，因此如果结果集认为它是 UTC 而不是我的本地时间，那么这样做的数学是不同的

解决方案

Kouber Saparev 的答案 大部分是正确的，虽然存储时区不正确.

Postgres 中的数据类型错误

<块引用>

UTC 中的时间戳.当我们的应用程序将该数据保存到 Postgres 时，它会将时间戳存储在一个没有时区的时间戳列中.

如他的回答中所述，您在 Postgres 数据库中使用了错误的数据类型.跟踪时刻时，您必须使用 TIMESTAMP WITH TIME ZONE 类型的列.在插入或更新期间提供输入时，任何有关时区或 UTC 偏移量的随附信息都用于调整为 UTC.然后丢弃伴随的区域/偏移.如果您需要记住原始区域/偏移量，则需要定义第二列并自己存储该信息.

Postgres 中的另一种类型，也是 SQL 标准，是 TIMESTAMP WITHOUT TIME ZONE.这种类型故意缺少任何时区或 UTC 偏移量的概念.所以这种类型不能代表时刻，不能在时间线上存储点.它存储代表潜在时刻的值，范围大约为 26-27 小时，即全球不同时区的范围.仅当您的意思是在任何地方或任何地方都有时间的日期时才使用这种类型，但不是特定的某个地方.也用于指未来的约会时间足够远，以至于我们冒着政治家更改我们关心的任何时区使用的偏移量的风险.

总是指定时区

<块引用>

我们商店中 postgres 的默认设置为我们的当地时间，山地时间

永远不要依赖于您的主机操作系统、数据库服务器或您的工具(例如 Java 虚拟机)的当前默认时区.始终在代码中指定所需/预期的时区.

提示:通常最好使用 UTC 进行数据存储、数据交换和大部分业务逻辑.从 UTC 调整到某个时区，仅用于向用户展示或在业务规则需要时.

如上所述，Postgres 总是以 UTC 或根本没有区域/偏移量存储日期时间值.注意:您和 Postgres 之间使用的工具可能会对从数据库中检索到的 UTC 值应用时区.虽然是善意的，但这个反特性造成了这样的错觉，即时区被存储，而实际上只有 UTC 存储在 TIMESTAMP WITH TIME ZONE 或 TIMESTAMP WITHOUT 中根本没有区域/偏移时区.

请注意，输入到 TIMESTAMP WITHOUT TIME ZONE 列的任何区域信息都会被忽略，日期和时间按原样存储.

<块引用>

我需要对该时间戳执行一些偏移(将其移动到，比如说 EST)

通常最好将数据库仅用于数据的存储、查询和检索.要调整时区等数据，请在您的应用程序中执行此类工作.例如，在 Java 中使用行业领先的 java.time 类，在 .NET 中使用 Noda Time 项目(java.time 的前身，Joda-Time 项目).

使用 JDBC 4.2 或更高版本的 Java 代码示例.

LocalDateTime

对于 TIMESTAMP WITHOUT TIME ZONE 列中的值，我们使用 Java 中的相应类型 LocalDateTime，没有任何时区概念或 offset-from-UTC.

LocalDateTime ldt = myResultSet.getObject( … , LocalDateTime.class ) ;//从数据库中检索值.字符串输出 = ldt.toString() ;//以标准 ISO 8601 格式生成表示此日期时间值的文本.

<块引用>

2018-01-23T01:23:45.123

如果您确定知道此日期和时间适用于 UTC，但在没有任何区域/偏移量信息的情况下被错误存储，您可以应用区域或偏移量来修复损坏.

OffsetDateTime odt = ldt.atOffset( ZoneOffset.UTC );//对缺少此类信息的 `LocalDateTime` 应用 UTC 偏移量.决定片刻.

OffsetDateTime

对于TIMESTAMP WITH TIME ZONE的一列中的值，我们使用Java中对应的类型，OffsetDateTime(或Instant)，表示UTC 时间.

OffsetDateTime odt = myResultSet.getObject( … , OffsetDateTime.class ) ;//从数据库中检索值.字符串输出 = odt.toString() ;//以标准 ISO 8601 格式生成表示此日期时间值的文本.末尾的Z"表示 UTC，发音为Zulu".

<块引用>

2018-01-23T01:23:45.123Z

ZonedDateTime

要通过北美中西部地区人民使用的挂钟时间的镜头查看以 UTC 设置的 OffsetDateTime 值，请指定时区，例如 美国/埃德蒙顿或美国/丹佛.

指定正确的时区名称，格式为continent/regioncode>，例如 America/Montreal、Africa/Cas​​ablanca 或 Pacific/Auckland.永远不要使用 2-4 个字母的缩写，例如 EST 或 IST，因为它们不是真正的时区，没有标准化，甚至不是唯一的(！).

ZoneId z = ZoneId.of("美国/丹佛") ;ZonedDateTime zdt = odt.atZoneSameInstant(z);

查看此 代码在 IdeOne.com 上实时运行.我们看到了相同的时刻，但挂钟时间不同.

<块引用>

2018-01-22T18:23:45.123-07:00[美国/丹佛]

当心工具和中间件注入时区

不幸的是，许多工具和中间件会自愿将一些默认时区应用于从数据库中检索到的时刻.虽然出于善意，但实际上在检索时时区是在存储后添加的，但会造成区域已成为存储数据的一部分的错觉.这种反特征造成了很多混乱.我希望所有工具都清晰真实，以 UTC 格式报告存储的时刻.

如果您使用 Java，带有 JDBC 4.2 及更高版本，您可以交换 java.time (JSR 310)(tutorial) 对象与数据库并避免此时区注入.

We have an application that fetches data from a source and that source present the data with a timestamp in UTC. When our application saves that data to Postgres, it stores that timestamp in a timestamp column without time zone. The default on postgres in our shop is set to our local time, Mountain Time. So that means, I think, that postgres assumes that timestamp is mountain time. How can I query that column so that my result set thinks it's UTC and not the local time zone?

More cleary stated, I need to perform some offsets on that timestamp (moving it to, say EST) and so the math of doing that is different if the resultset thinks it's UTC than my local time

解决方案

The Answer by Kouber Saparev is mostly correct, though incorrect about storing a time zone.

Wrong data type in Postgres

a timestamp in UTC. When our application saves that data to Postgres, it stores that timestamp in a timestamp column without time zone.

As noted in his Answer, you are using the wrong data type in your Postgres database. When tracking moments, you must use a column of type TIMESTAMP WITH TIME ZONE. When supplying an input during an insert or update, any accompanying info about time zone or offset-from-UTC is used to adjust into UTC. The accompanying zone/offset is then discarded. If you need to remember the original zone/offset, you will need to define a second column and store that info there yourself.

The other type in Postgres, and the SQL standard, is TIMESTAMP WITHOUT TIME ZONE. This type purposely lacks any concept of time zone or offset-from-UTC. So this type cannot represent moments, cannot store points on the timeline. It stores values that represent potential moments along a range of about 26-27 hours, the range of various time zones around the globe. Use this type only when you mean a date with time-of-day everywhere or anywhere, but not specifically somewhere. Also used when you mean appointments far enough out in the future that we run the risk of politicians changing the offset used in any of the time zones we care about.

Always specify time zone

default on postgres in our shop is set to our local time, Mountain Time

Never depend on the current default time zone of your host OS, the database server, or your tools such as the Java Virtual Machine. Always specify the desired/expected time zone in your code.

Tip: Generally best to work in UTC for data storage, data exchange, and most of your business logic. Adjust from UTC to a time zone only for presentation to the user or where business rules require.

As explained above, Postgres always stores date-time values either in UTC or with no zone/offset at all. Beware: Tools used between you and Postgres may apply a time zone to the UTC value retrieved from the database. While well-intentioned, this anti-feature creates the illusion that the time zone was stored when in fact only UTC was stored in TIMESTAMP WITH TIME ZONE or no zone/offset at all in TIMESTAMP WITHOUT TIME ZONE.

Be aware that any zone information accompanying input to a column of TIMESTAMP WITHOUT TIME ZONE is simply ignored, the date and time-of-day taken as-is and stored.

I need to perform some offsets on that timestamp (moving it to, say EST)

Generally best to use your database just for storage, query, and retrieval of data. For massaging the data like adjusting time zone, do such work in your application. For example, in Java use the industry-leading java.time classes, in .NET the Noda Time project (a port of the predecessor of java.time, the Joda-Time project).

Example code in Java using JDBC 4.2 or later.

LocalDateTime

For a value in a column of TIMESTAMP WITHOUT TIME ZONE we use the corresponding type in Java, LocalDateTime, lacking any concept of time zone or offset-from-UTC.

LocalDateTime ldt = myResultSet.getObject( … , LocalDateTime.class ) ;  // Retrieve value from database.
String output = ldt.toString() ;  // Generate text representing this date-with-time value in standard ISO 8601 format.

2018-01-23T01:23:45.123

If you know for certain that this date and time was meant for UTC but was incorrectly stored without any zone/offset info, you can apply a zone or offset to repair the damage.

OffsetDateTime odt = ldt.atOffset( ZoneOffset.UTC );  // Apply an offset-from-UTC to a `LocalDateTime` lacking such information. Determines a moment.

OffsetDateTime

For a value in a column of TIMESTAMP WITH TIME ZONE we use the corresponding type in Java, OffsetDateTime (or Instant), representing a moment in UTC.

OffsetDateTime odt = myResultSet.getObject( … , OffsetDateTime.class ) ;  // Retrieve value from database.
String output = odt.toString() ;  // Generate text representing this date-with-time value in standard ISO 8601 format. A `Z` on the end indicates UTC, pronounced "Zulu". 

2018-01-23T01:23:45.123Z

ZonedDateTime

To see that OffsetDateTime value set in UTC through the lens of the wall-clock time used by the people of regions within the mid-west of North America, specify a time zone such as America/Edmonton or America/Denver.

Specify a proper time zone name in the format of continent/region, such as America/Montreal, Africa/Casablanca, or Pacific/Auckland. Never use the 2-4 letter abbreviation such as EST or IST as they are not true time zones, not standardized, and not even unique(!).

ZoneId z = ZoneId.of( "America/Denver" ) ;  
ZonedDateTime zdt = odt.atZoneSameInstant( z ) ;

See this code run live at IdeOne.com. We see the same moment but with a different wall-clock time.

2018-01-22T18:23:45.123-07:00[America/Denver]

Beware of tools & middleware injecting a time zone

Unfortunately, many tools and middleware will volunteer to apply some default time zone to a moment retrieved from the database. While well-intentioned, this creates the illusion of the zone having been a part of the stored data when in fact the time zone was added after storage, upon retrieval. This anti-feature creates much confusion. I wish all the tools were clear and truthful by reporting the moment in UTC, as it was stored.

If you use Java, with JDBC 4.2 and later, you can exchange java.time (JSR 310) (tutorial) objects with the database and avoid this time zone injection.

这篇关于我如何告诉 postgres 列中的时间戳是 UTC?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！