PostgreSQL date() with timezone

Question

我在从 Postgres 正确选择日期时遇到问题 - 它们以 UTC 格式存储，但是未正确使用 Date（） 函数进行转换。

如果时间戳超过

太平洋标准时间下午 4 点，将时间戳转换为日期会给我错误的日期。

在这种情况下，2012-06-21应该2012-06-20。

starts_at列数据类型为 timestamp without time zone 。以下是我的查询：

在不转换为 PST 时区的情况下：

Select starts_at from schedules where id = 40;
      starts_at      
---------------------
 2012-06-21 01:00:00

转换给出：

Select (starts_at at time zone 'pst') from schedules where id = 40;
        timezone        
------------------------
 2012-06-21 02:00:00-07

但两者都不会转换为时区中的正确日期。

Answer 1

基本上你想要的是：

$ select starts_at AT TIME ZONE 'UTC' AT TIME ZONE 'US/Pacific' from schedules where id = 40

我从这篇文章中得到了解决方案如下，这是直接的黄金!!它非常清楚地解释了这个重要的问题，如果您想更好地了解 pstgrsql TZ 管理，请阅读它。

以本地时间表示不带区域的 PostgreSQL 时间戳

这是正在发生的事情。首先，您应该知道'PST 时区比 UTC 时区晚 8 小时，因此例如 2014 年 1 月 1 日下午 4：30 PST（2014 年 1 月 1 日星期三 16：00：30 -0800）相当于 2014 年 1 月 2 日上午 00：30 UTC（2014 年 1 月 2 日星期四 00：00：30 +0000）。太平洋标准时间下午 4：00 之后的任何时间都会滑到第二天，解释为 UTC。

此外，正如上面提到的Erwin Brandstetter，postresql有两种类型的时间戳数据类型，一种有时区，一种没有时区。如果您的时间戳包含时区，则简单：

$ select starts_at AT TIME ZONE 'US/Pacific' from schedules where id = 40

会工作。但是，如果您的时间戳是无时区的，则执行上述命令将不起作用，您必须首先将无时区时间戳转换为具有时区的时间戳，即 UTC 时区，然后才将其转换为所需的"PST"或"美国/太平洋"（在某些夏令时问题上是相同的。我想你应该没问题）。

让我用一个创建无时区时间戳的示例进行演示。为了方便起见，让我们假设我们的本地时区确实是"PST"（如果不是，那么它会变得稍微复杂一点，这对于本解释来说是不必要的）。

假设我有：

$ select timestamp '2014-01-2 00:30:00' AS a, timestamp '2014-01-2 00:30:00' AT TIME ZONE 'UTC' AS b,  timestamp '2014-01-2 00:30:00' AT TIME ZONE 'UTC' AT TIME ZONE 'PST' AS c, timestamp '2014-01-2 00:30:00' AT TIME ZONE 'PST' AS d

这将产生：

"a"=>"2014-01-02 00:30:00"   (This is the timezoneless timestamp)
"b"=>"2014-01-02 00:30:00+00" (This is the UTC TZ timestamp, note that up to a timezone, it is equivalent to the timezoneless one)
"c"=>"2014-01-01 16:30:00" (This is the correct 'PST' TZ conversion of the UTC timezone, if you read the documentation postgresql will not print the actual TZ for this conversion)
"d"=>"2014-01-02 08:30:00+00"

最后一个时间戳是关于在 postgresql 中将无时区时间戳从 UTC 转换为"PST"的所有混淆的原因。当我们写：

timestamp '2014-01-2 00:30:00' AT TIME ZONE 'PST' AS d

我们正在获取一个无时区的时间戳并尝试将其转换为'PST TZ（我们间接假设postgresql会理解我们希望它从UTC TZ转换时间戳，但postresql有自己的计划！实际上，postgresql 所做的是获取无时区时间戳 （'2014-01-2 00：30：00） 并将其视为已经是"PST"TZ 时间戳（即：2014-01-2 00：30：00 -0800）并将其转换为 UTC 时区!!所以它实际上提前了 8 小时而不是后推！因此我们得到（2014-01-02 08：30：00+00）。

无论如何，这最后一种（不直观的）行为是所有混乱的原因。如果您想进行更彻底的解释，请阅读这篇文章，我实际上得到的结果与最后一部分的结果略有不同，但总体思路是相同的。

Answer 2

我在您的问题中没有看到确切的starts_at类型。您确实应该包含此信息，这是解决方案的关键。我得猜猜。

PostgreSQL 始终在内部存储类型timestamp with time zone的 UTC 时间。输入和输出（显示）根据当前timezone设置或给定时区进行调整。AT TIME ZONE的效果也会随基础数据类型而变化。看：

• 在 Rails 和 PostgreSQL 中完全忽略时区

如果从类型 timestamp [without time zone] 中提取date，则会获得当前时区的日期。输出中的日期将与显示timestamp值中的日期相同。

如果从类型 timestamp with time zone（简称 timestamptz）中提取date，则首先"应用"时区偏移量。您仍然可以获取当前时区的日期，该日期与时间戳的显示一致。在欧洲部分地区，相同的时间点可以转换为第二天，例如加利福尼亚州的下午4点多。要获取特定时区的日期，请先应用AT TIME ZONE。

因此，您在问题顶部描述的内容与您的示例相矛盾。

假设starts_at是timestamp [without time zone]并且服务器上的时间设置为本地时间。测试方法：

SELECT now();

它显示的时间是否与墙上的时钟相同？如果是（并且数据库服务器以正确的时间运行），则当前会话的timezone设置与本地时区一致。如果没有，您可能需要访问postgresql.conf或客户端中的会话timezone设置。手册中的详细信息。

请注意，timezone偏移量使用的符号与时间戳文本中显示的符号相反。看：

• Postgres数据库中的特殊时区处理

从starts_at获取您的本地日期

SELECT starts_at::date

相当于：

SELECT date(starts_at)

顺便说一句，您的当地时间现在是 UTC-7，而不是 UTC-8，因为夏令时是有效的（不在人类更聪明的想法中）。

太平洋标准时间 （PST） 通常比 UTC（世界时区）早 8 小时（timestamp值较大），但在夏令时（如现在）可能是 7 小时。这就是为什么在您的示例中timestamptz显示为2012-06-21 02:00:00 -07的原因。构造AT TIME ZONE 'PST'考虑了夏令时。这两个表达式产生不同的结果（一个在冬天，一个在夏天），并且在投射时可能会导致不同的日期：

SELECT '2012-06-21 01:00:00'::timestamp AT TIME ZONE 'PST'
     , '2012-12-21 01:00:00'::timestamp AT TIME ZONE 'PST'

Answer 3

我知道这是一个旧的，但您可能需要考虑在投射时使用时区"美国/太平洋"以避免任何 PST/PDT 问题。 所以

SELECT starts_at::TIMESTAMPTZ AT TIME ZONE "US/Pacific" 
  FROM schedules 
 WHERE ID = '40';