postgresql 带时区的 Postgres 时间戳

Postgres timestamp with timezone

提问by Waseem

I have column 'event_date_time' in my 'events' table with type 'timestamp with timezone'. My python flask application is saving date like '2014-08-30 02:17:02+00:00'but postgres automatically converts it to '2014-08-30 07:17:02+05'. It converts the timestamp to my local timezone i-e Pakistan. I want to save it without converting. I have tried

我的“事件”表中有“event_date_time”列，类型为“timestamp with timezone”。我的 python Flask 应用程序正在保存日期，'2014-08-30 02:17:02+00:00'但 postgres 会自动将其转换为'2014-08-30 07:17:02+05'. 它将时间戳转换为我的本地时区，即巴基斯坦。我想保存它而不转换。我试过了

set timezone='UTC'

设置时区='UTC'

and it does change timezone to 'UTC' but pgadmin3 is still saving the converted time.

它确实将时区更改为“UTC”，但 pgadmin3 仍在保存转换后的时间。

I am using MAC OS and Postgresql 9.3.

我正在使用 MAC 操作系统和 Postgresql 9.3。

回答by Lucas

The reason pgadmin is displaying hours +5 is because your system timezone is set to this. When you save a "timestamp with time zone" value at GMT + or - any value, the system offsets whatever timezone your input was to GMT (or UTC), so that when you go to retrieve it, you can specify the timezone you want it displayed in.

pgadmin 显示小时数 +5 的原因是因为您的系统时区设置为此。当您在 GMT + 或 - 任何值保存“带时区的时间戳”值时，系统会将您输入的任何时区偏移到 GMT（或 UTC），以便在您检索它时，您可以指定所需的时区它显示在。

For example let's establish a current time for say... New York.

例如，让我们建立一个当前时间，例如……纽约。

select now()::timestamp with time zone at time zone 'America/New_York';

select now()::timestamp with time zone at time zone 'America/New_York';

At the time of asking it returned '2014-08-23 08:50:57.136817'. 8:50 Saturday morning, or 8:51 if you're being pedantic.

在询问时，它返回了“2014-08-23 08:50:57.136817”。周六早上 8:50，或者如果你很迂腐，则是 8:51。

Now if we take that same time and display it in GMT we will see a different result:

现在，如果我们使用相同的时间并以 GMT 显示它，我们将看到不同的结果：

select '2014-08-23 08:50:57.136817 America/New_York'::timestamp with time zone at time zone 'GMT';

select '2014-08-23 08:50:57.136817 America/New_York'::timestamp with time zone at time zone 'GMT';

Now have a new time of '2014-08-23 12:50:57.136817'... 5 hours into the "future"!

现在有一个新的时间'2014-08-23 12:50:57.136817'......进入“未来”5小时！

Finally let's get the original timestamp and display it in what I believe is the Pakistan time zone (PKT) and see what it shows

最后让我们获取原始时间戳并将其显示在我认为是巴基斯坦时区 (PKT) 中并查看它显示的内容

select '2014-08-23 08:50:57.136817 America/New_York'::timestamp with time zone at time zone 'PKT';

select '2014-08-23 08:50:57.136817 America/New_York'::timestamp with time zone at time zone 'PKT';

The result? '2014-08-23 17:50:57.136817' further into the future still!

结果？'2014-08-23 17:50:57.136817' 更远的未来！

Again I must stress the reason it can do this is because it is always converting the input time offset to UTC or GMT. Postgres processes all of its "timestamp with time zone" data types in this way. It is designed to avoid time zone problems such as daylight savings and so on.

我必须再次强调它可以这样做的原因是因为它总是将输入时间偏移量转换为 UTC 或 GMT。Postgres 以这种方式处理其所有“带时区的时间戳”数据类型。它旨在避免时区问题，例如夏令时等。

Your issue appears to be that python is inserting the time at an offset of +00, and if this was supposed to be a local time then you will be 5 hours off as far as postgres is concerned. Without knowing exactly what queries python is making, I would assume you may want to look at that to make sure it is giving you the correct time, presumably set timezone='PKT'should be a fix. Either way, when you are viewing timestamp with time zone using a browser such as pgadmin, the timestamp is being converted to your local timezone and this is why you see +5.

您的问题似乎是 python 以 +00 的偏移量插入时间，如果这应该是本地时间，那么就 postgres 而言，您将有 5 小时的时间。在不确切知道 python 正在做什么查询的情况下，我假设您可能想要查看它以确保它为您提供正确的时间，大概set timezone='PKT'应该是一个修复。无论哪种方式，当您使用 pgadmin 等浏览器查看带时区的时间戳时，时间戳都会转换为您的本地时区，这就是您看到 +5 的原因。

Alternatively if you do wish to see those times at +00 then you must specify that you want this in your SELECT queries.

或者，如果您确实希望在 +00 处看到这些时间，那么您必须在 SELECT 查询中指定您想要这个。