Postgres带时区的时间戳

Question

我在我的 'events' 表中有一个名为 'event_date_time' 的列，类型为 'timestamp with timezone'。我的 Python Flask 应用程序保存日期的格式如下： '2014-08-30 02:17:02+00:00'，但是PostgreSQL会自动将其转换为 '2014-08-30 07:17:02+05'。它会将时间戳转换为我所在时区的本地时间，即巴基斯坦。我希望保存它而不进行转换。

我尝试了

set timezone='UTC'

但它只更改时区为“UTC”，而pgadmin3仍然保存转换后的时间。

我使用的操作系统是MAC OS，数据库是PostgreSQL 9.3。

- Waseem

---

你的意思是你的选择查询返回的时间是GMT+5，而你要么希望你的Python插入遵循这个时间而不是GMT+2，要么希望在GMT+0选择？无论哪种方式，带时区的时间戳隐式地将所有条目保存为UTC并在选择时转换时间。 - Lucas

我认为该字段应该是“没有时区的时间戳”。 - Vivek S.

@Lucas 如果带时区的时间戳隐式地将所有条目保存为UTC，那么为什么pgadmin显示的时间是GMT+5？ - Waseem

@WingedPanther 我尝试了没有时区的时间戳，它可以工作。谢谢。但是带有时区的时间戳也应该可以工作吧？ - Waseem

Answer 1

pgadmin显示时间+5的原因是因为你的系统时区设置成了这个。当你在GMT +或-任何值下保存一个“带有时区的时间戳”值时，系统会将你输入的任何时区偏移量转换为GMT（或UTC），以便在检索它时，你可以指定要显示的时区。例如，让我们建立一个当前时间，比如...纽约。

选择 now() ::timestamp with time zone at time zone 'America/New_York'; 。在询问时，它返回“2014-08-23 08:50:57.136817”。周六早上8:50，或者如果你很严谨，就是8:51。现在，如果我们取同样的时间并在GMT中显示它，我们将看到不同的结果：

选择 '2014-08-23 08:50:57.136817 America/New_York' ::timestamp with time zone at time zone 'GMT';。现在有一个新的时间“2014-08-23 12:50:57.136817”...进入“未来”的5个小时！最后，让我们获取原始时间戳并在我认为是巴基斯坦时区（PKT）中显示它，看看它显示了什么

选择 '2014-08-23 08:50:57.136817 America / New_York' ::timestamp with time zone at time zone 'PKT';。结果是仍然更加进入未来的“2014-08-23 17:50:57.136817”！再次强调，它能够这样做的原因是它始终将输入时间偏移量转换为UTC或GMT。Postgres以这种方式处理其全部的“带有时区的时间戳”数据类型，旨在避免诸如夏令时等时区问题。

您的问题似乎是Python将时间插入到+00的偏移量中，如果这应该是本地时间，则Postgres会认为您相差5个小时。不知道Python正在进行什么查询，我会假设您可能需要查看一下确保它给出了正确的时间，大概率是set timezone='PKT'可以解决问题。无论如何，当您使用诸如pgadmin之类的浏览器查看带有时区时间戳时，时间戳都会被转换为您的本地时区，这就是为什么您会看到+5的原因。

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

- Lucas