在 PostgreSQL 中，我们可以直接比较两个不同时区的时间戳吗?

Question

SELECT * FROM table_a 
WHERE time_1 >= to_timestamp('11/01/2014 10:00 PDT', 'MM/DD/YYYY HH24:MI TZ')

time_1采用 UTC 时区，它是时区的时间戳。那么，这会给我我想要的东西，还是我需要在函数 to_timestamp(( 中执行确切的 UTC 时间？

Answer 1

Postgresql 有两种不同的时间戳数据类型，当应该使用哪一种时，令人困惑。 这两种类型是：

• timestamp(也称为timestamp without time zone(很可能是table_a中的类型
• timestamp with time zone 这是 to_timestamp(( 返回的数据类型

您必须确保将苹果与苹果进行比较或将成对与成对进行比较，不要将它们混合，否则可能会得到不良结果。

如果您的table_a.time_1是timestamp with time zone那么您在问题中给出的代码将正常工作。

如果您的table_a.time_1是timestamp则需要更改代码：

SELECT * 
  FROM table_a 
 WHERE time_1 >= to_timestamp('11/01/2014 10:00 PDT', 'MM/DD/YYYY HH24:MI TZ') at time zone 'utc';

此内容的最后一部分(at time zone 'utc'(将去除时区(PDT(的指定时间戳，并将时间戳转换为UTC。

---

编辑：为了帮助您在此答案中的评论...

为了了解如何翻译时区，您需要了解两种形式的时间戳之间的差异。 下面将清楚为什么您需要了解这一点。 正如我上面指出的，两种形式的时间戳之间的差异令人困惑。有一个很好的手册页，但现在只是继续阅读。

要了解的主要事情是，这两个版本实际上都没有存储时区(尽管名称如此(。 如果您添加一个额外的单词"翻译">，命名会更有意义。 想想">没有时区转换的时间戳"和"有时区转换的时间戳"。

timestamp with time zone翻译根本不存储时区。 它旨在存储可能来自世界任何地方的时间戳，而不会失去其含义。 因此，在输入一个时，您必须提供它来自的时区，否则 postgresql 将假定它来自您当前会话的时区。 Postgresql 会自动将其从给定时区转换为服务器的内部时区。 你不需要知道那是什么时区，因为postgresql在给你值之前总是会把它从这个内部时区转换回来。当您检索值(例如：SELECT my_time FROM foo(时，postgresql 会将时间戳转换为当前会话的时区。 或者，您可以指定要转换为的时区(例如：SELECT my_time AT TIME ZONE 'PDT' FROM foo(。

考虑到这一点，更容易理解的是，没有时区转换的timestamp永远不会从您指定的时间开始更改。 Postgresql 会认为11:00:00发生在12:00:00之前，即使你的意思是在美国有 11 个，在英国有 12 个。 很容易看出为什么这可能不是你想要的。

一个非常常见的编程错误是认为timestamp with time zone位于特定时区。 其实不然。 无论您要求它位于哪个时区。 如果你没有指定你想要的时区，那么postgresql将假设你想要它在当前会话时区。

你说过你的领域是一个timestamp with time zone，都在UTC. 这在技术上是不正确的。 很可能您的会话时区是UTC，因此postgresql为您提供UTC格式的所有内容。

所以你有一个timestamp with time zone，你想知道PDT的这些时间是什么？ 简单：SELECT my_time AT TIME ZONE 'PDT' FROM foo。

重要的是要了解AT TIME ZONE '...'语法在timestamp和timestamp with time zone之间切换。

• timestamp AT TIME ZONE 'PDT'转换为timestamp with time zone，并告诉 postgresql 转换为 PDT 时区。
• timestamp with time zone AT TIME ZONE 'PDT'转换为一个timestamp，告诉postgresql将其解释为来自"PDT"。

这种对称性意味着要反转AT TIME ZONE 'foo'您只需使用AT TIME ZONE 'foo' . 换句话说SELECT anything AT TIME ZONE 'PDT' AT TIME ZONE 'PDT'将始终保持anything不变。