postgresql Postgres 时间戳

Postgres Timestamp

提问by Scottf007

We are having a debate about the best way to store a timestamp in postgres. Currently all time stamps are stored as +00 and we have a timezone associated with each client. We look up the timezone and convert the time that something happened which increases complexity as we need to do more joins and a more complex query.

我们正在讨论在 postgres 中存储时间戳的最佳方式。目前所有时间戳都存储为 +00，我们有一个与每个客户端关联的时区。我们查找时区并转换发生某些事情的时间，这会增加复杂性，因为我们需要进行更多的连接和更复杂的查询。

Another method is connecting to Postgres and setting the timezone of the connection and it changes all the times to be that timezone.

另一种方法是连接到 Postgres 并设置连接的时区，它会一直更改为该时区。

My problem with this is that in ANZ there are 4-5 timezones. When we try and do our invoices we need to know what day certain transactions happened, and across three timezones there is no perfect solution.

我的问题是在 ANZ 中有 4-5 个时区。当我们尝试开具发票时，我们需要知道某些交易发生在哪一天，并且跨越三个时区并没有完美的解决方案。

I was thinking of including the timezone in the timestamp to make it easier - TIMESTAMP '1999-01-15 8:00:00 -8:00'

我正在考虑在时间戳中包含时区以使其更容易 - TIMESTAMP '1999-01-15 8:00:00 -8:00'

I was under the impression that this was best practice, but some people say this is a bad idea. We will have customers across ANZ that we need to do accurate invoices for, what is the best solution and most elegant?

我的印象是这是最佳实践，但有些人说这是一个坏主意。我们将有 ANZ 的客户需要我们为其提供准确的发票，最好的解决方案和最优雅的解决方案是什么？

Cheers Scott

干杯斯科特

回答by leonbloy

There are no bulletproof solutions here.

这里没有防弹解决方案。

My first advice: never rely on the default timezone of the server.

我的第一个建议：永远不要依赖服务器的默认时区。

My second advice: choose between timestamp-timestamptzaccording to the (predominant) semantics of the data.

我的第二个建议是：选择timestamp-timestamptz根据数据的（主要）语义。

In more detail: PostgresSQL has two timestamp variants, confusingly named TIMESTAMP WITHOUT TIMEZONE (timestamp)and TIMESTAMP WITH TIMEZONE (timestamptz). Actually, neitherstores a timezone, nor even an offset. Both datatypes occupy the same width (4 bytes), and their difference is subtle - and, worse, can bite you if you don't fully understand them and your server changes the timezone. My sanity ruleset is:

更详细地说：PostgresSQL 有两个时间戳变体，名称混淆了TIMESTAMP WITHOUT TIMEZONE (timestamp)和TIMESTAMP WITH TIMEZONE (timestamptz). 实际上，既不存储时区，也不存储偏移量。两种数据类型都占用相同的宽度（4 个字节），它们的区别很微妙 - 更糟糕的是，如果您不完全理解它们并且您的服务器更改了时区，可能会咬您。我的理智规则集是：

• Use TIMESTAMP WITH TIMEZONE (timestamptz)for storing events that are predominantly related to the "physical" time, for which you are mainly interested in querying whether event 1was before event 2(regardless of timezones), or computing time intervals (in "physical units", eg, seconds; not in "civil" units as days-months, etc). The typical example are record creation/modification time - what one usually means by the word "Timestamp".

• Use TIMESTAMP WITHOUT TIMEZONE (timestamp)for storing events for which the relevant information is the "civil time"(that is, the fields {year-month-day hour-min-sec}as a whole), and the queries involve calendar calculations. In this case, you would store here only the "local time", i.e., the date-time relative to some unspecified (irrelevant, or implied, or stored somewhere else) timezone.

• 使用TIMESTAMP WITH TIMEZONE (timestamptz)用于存储主要是相关的事件“物理”的时间，为你的主要兴趣在询问是否event 1是之前event 2（不管时区），或计算的时间间隔（以“物理单位”，例如，秒;不是“民用”单位为天-月等）。典型的例子是记录创建/修改时间 - “时间戳”一词通常意味着什么。

• 使用TIMESTAMP WITHOUT TIMEZONE (timestamp)用于存储该相关信息是事件的“民间时间”（即字段{year-month-day hour-min-sec}作为一个整体），和查询涉及日历计算。在这种情况下，您将仅在此处存储“本地时间”，即相对于某个未指定（不相关、或隐含或存储在其他地方）时区的日期时间。

The second option makes you easier to query for, say, "all events that happened on day '2013-01-20'" (in each corresponding region/country/timezone) - but makes it more difficult to query for "all events that ocurred (physically) before a reference event" (unless we know they are in the same timezone). You choose.

第二个选项使您更容易查询，例如，“发生在 '2013-01-20' 日的所有事件”（在每个相应的地区/国家/时区） - 但使查询“所有事件”变得更加困难在参考事件之前（物理上）发生”（除非我们知道它们在同一时区）。你选。

If you need the full thing, neither is enough, you need either to store the timezone or the offset in an additional field. Another option, which wastes a few bytes but can be more efficient for queries is to store both fields.

如果您需要完整的内容，两者都不够，您需要将时区或偏移量存储在附加字段中。另一种浪费几个字节但对查询更有效的选择是存储两个字段。

See also this answer.

另请参阅此答案。

回答by Lucas

Use timestamptz(or timestamp with time zonefor standard SQL syntax) for your input fields, you can then set a custom time offset for every insert using either the timezone or time offset to whatever suits your preference.

对输入字段使用timestamptz（或timestamp with time zone用于标准 SQL 语法），然后您可以使用时区或时间偏移为每个插入设置自定义时间偏移，以适合您的偏好。

Example…

例子…

CREATE TABLE "timetest"
(
"timestamp" timestamptz
);

INSERT INTO "timetest" ("timestamp") VALUES ('2013-01-01 08:45:00 PST');
INSERT INTO "timetest" ("timestamp") VALUES ('2013-01-01 08:45:00 Europe/Madrid');
INSERT INTO "timetest" ("timestamp") VALUES ('2013-01-01 08:45:00 Europe/Athens');
INSERT INTO "timetest" ("timestamp") VALUES ('2013-01-01 08:45:00 GMT+11');
INSERT INTO "timetest" ("timestamp") VALUES ('2013-01-01 08:45:00 GMT-11');
INSERT INTO "timetest" ("timestamp") VALUES ('2013-01-01 08:45:00 UTC');

...and your times will be adjusted accordingly

...您的时间将相应调整

SELECT * FROM "timetest"; -- note this may default to your timezone
------------------------
[timestamp]
------------------------
2013-01-01 16:45:00+00
2013-01-01 07:45:00+00
2013-01-01 06:45:00+00
2012-12-31 21:45:00+00
2013-01-01 19:45:00+00
2013-01-01 08:45:00+00
2013-01-01 08:45:00+00

or better still, try the following...

或者更好的是，尝试以下...

SELECT 
"timestamp" AT TIME ZONE 'Australia/Sydney' AS "Sydney", 
"timestamp" AT TIME ZONE 'Australia/Perth' AS "Perth" 
FROM "timetest";
--------------------------------------------
[Sydney]..............[Perth]
--------------------------------------------
2013-01-02 03:45:00 - 2013-01-02 00:45:00
2013-01-01 18:45:00 - 2013-01-01 15:45:00
2013-01-01 17:45:00 - 2013-01-01 14:45:00
2013-01-01 08:45:00 - 2013-01-01 05:45:00
2013-01-02 06:45:00 - 2013-01-02 03:45:00
2013-01-01 19:45:00 - 2013-01-01 16:45:00

Finally, to get an idea of list of the timezones availalble to your database try:

最后，要了解数据库可用的时区列表，请尝试：

SELECT * FROM pg_timezone_names ORDER BY utc_offset DESC;