具有时区的 PostgreSQL date()

我在选择 Postgres 的日期时遇到了问题-它们是以 UTC 格式存储的，但是 没有正确地使用 Date ()函数进行转换。

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

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

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

不转换为太平洋夏令时时区:

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

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

128855 次浏览
小开
最佳答案

我没有看到 一模一样类型的 starts_at在你的问题。你真的应该包括这些信息，这是解决方案的关键。我得猜猜。

PostgreSQL一直都是在内部存储类型 timestamp with time zone的 UTC 时间。输入和输出(显示)调整到当前的 timezone设置或给定的时区。AT TIME ZONE的效果也随着底层数据类型的变化而变化。参见:

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

如果从类型 timestamp with time zone(简称 timestamptz)中提取 date，则首先“应用”时区偏移量。您仍然可以得到当前时区的日期，这与时间戳的 展示一致。在欧洲部分地区，同样的时间点转换成第二天，比如在加利福尼亚州，那时已经过了下午4点。要获取特定时区的日期，首先应用 AT TIME ZONE。

因此，你在问题的开头所描述的与你的例子相矛盾。

假设 starts_at是 timestamp [without time zone]，并且服务器上的时间被设置为本地时间:

SELECT now();

它和你墙上的时钟显示的时间一样吗？如果是(并且 db 服务器运行的时间正确) ，则当前会话的 timezone设置与本地时区一致。如果没有，您可能需要访问您的 postgresql.conf中的 timezone设置或您的客户端的会话。手册上有详细说明。

请注意，timezone偏移量使用了与时间戳文字显示内容相反的 签字:

从 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'
小开

我知道这是一个旧的，但你可能要考虑使用 AT TIME ZONE“美国/太平洋”时铸造，以避免任何 PST/PDT 问题。那么

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

基本上你想要的是:

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

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

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

事情是这样的。首先你应该知道太平洋夏令时的时区比世界协调时晚8个小时，所以例如2014年1月1日，太平洋夏令时下午4:30(星期三，2014年1月1日16:00:30 -0800)相当于2014年1月2日，世界协调时上午00:30(星期四，2014年1月2日00:00:30 + 0000)。太平洋标准时间下午4点以后的任何时间都可以推迟到第二天，解释为世界协调时。

另外，正如 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"

最后一个时间戳造成了在 postgreql 中将无时区时间戳从 UTC 转换为‘ PST’的混乱。当我们写道:

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

We are taking a timezoneless timestamp and try to convert it to 'PST TZ (we indirectly assume that postgresql will understand that we want it to convert the timestamp from a UTC TZ, but postresql has plans of its own!). In practice, what postgresql does is it takes the timezoneless timestamp ('2014-01-2 00:30:00) and treats it as if it WERE ALREADY a 'PST' TZ timestamp (i.e: 2014-01-2 00:30:00 -0800) and converts that to UTC timezone!!! So it actually pushes it 8 hours ahead instead of back! Thus we get (2014-01-02 08:30:00+00).

无论如何，最后这个(不直观的)行为是所有困惑的原因。阅读这篇文章，如果你想要一个更彻底的解释，我实际上得到的结果有点不同，他们在这最后一部分，但总的想法是相同的。


提交答案