Postgres通过时区转换防止时间戳记 [英] Postgres prevent timestamp with timezone conversion

问题描述

我有一个表，用于存储带时区的iso日期。我意识到日期应该总是存储为utc，但该规则有一个例外。时间戳与它们在其上运行的服务器没有任何关系。我希望能够存储这样的iso日期：
2016-03-06T01：15：52-06：00
而且无论服务器的时区还是其他我想要的时间戳返回为：
2016-03-06T01：15：52-06：00

I have a table that I am using to store iso dates with timezones. I realize that dates should "always" be stored as utc but I have an exception to that rule. The timestamps aren't in any way related to the server they are running on. I want to be able to store an iso date like this: 2016-03-06T01:15:52-06:00 And regardless of the time zone of the server or anything else I want the timestamp returned as: 2016-03-06T01:15:52-06:00

当前，如果我插入iso日期，它将自动将其转换为任何服务器时区是。我的上述日期被转换为：
2016-03-06 07：15：52 + 00（服务器是utc）

Currently if I insert an iso date it automatically converts it to whatever the server timezone is. My above date gets converted to: 2016-03-06 07:15:52+00 (server is utc)

我唯一想到的是将时区偏移量存储在单独的列中，将我的日期存储为utc，然后使用偏移量列进行转换，这非常混乱。当然，有一种方法可以将我的日期存储在一个列中，并使其以最初创建的方式显示出来？

The only thing I can think of is storing the timezone offset in a separate column, storing my date as utc and then converting using the offset column, horribly messy. Surely there is a way to store my date in one column and get it out the way it was originally created?

推荐答案

您建议的解决方案是正确的。更确切地说，它是几种正确的实现之一。以下任何一种方法均可：

Your proposed solution is correct. Or more precisely, it is one of several correct implementations. Any of the following would work:

• 将UTC时间戳存储在一个字段中，将偏移量存储在另一个字段中。


• 将本地时间戳存储在一个字段中，将偏移量存储在另一个字段中。


• 存储本地日期在一个字段中，并将带时区的时间存储在另一个字段中。 （虽然通常不建议使用带有时区的时间 ...）


• 将UTC时间戳存储在一个字段中，将本地时间戳存储在另一个字段中。

• Store the UTC timestamp in one field, store the offset in another.
• Store the local timestamp in one field, store the offset in another.
• Store the local date in one field, and store a time with time zone in another. (though time with time zone is generally discouraged...)
• Store the UTC timestamps in one field and the local timestamp in another.

到目前为止，最简单的是您已经提出的第一个。

The easiest by far is the first one, which you already proposed.

我会避免将时间戳记存储在 text 字段中，因为它们往往无法非常有效地进行搜索。

I'd avoid against storing timestamps in text fields, as they tend not to be very efficiently searchable.

另外请注意-如果您来自SQL Server背景，则可能会想起它的 datetimeoffset 类型，该类型将本地日期时间和偏移量存储在字段中，并使用索引期间的UTC等效项。人们通常认为Postgres和MySQL的带时区的时间戳会具有相同的行为，但实际上却没有。他们只是使用 session 时区与UTC进行转换。 SQL Server没有会话时区的概念，因此也没有差异。

Also note - if you're coming from a SQL Server background, you might recall its datetimeoffset type, which stores the local datetime and offset in the field, and uses the UTC equivalent during indexing. It's common to think that Postgres and MySQL's timestamp with time zone would have the same behavior, but they don't. They simply use the session time zone to convert to/from UTC. SQL Server has no concept of a session time zone, and thus the discrepancy.

请务必阅读这部分Postgres文档。

这篇关于Postgres通过时区转换防止时间戳记的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！