正在获取带有时区偏移量的日期

Question

我正试图从postgres中的查询中提取日期。时间戳存储为UTC，所以如果我有2014年1月1日02:00:00，我希望太平洋时间的日期是2013年12月31日，而不是2014年1日。我真的很接近，但查询2和查询3仍然返回2014年1月1日。

SELECT '1-1-2014 02:00:00'::timestamp at time zone 'America/Los_Angeles';

返回

2014-01-01 02:00:00-08

-

SELECT CAST('1-1-2014 02:00:00'::timestamp at time zone 'America/Los_Angeles' AS Date);

返回

2014-01-01

但我希望它返回CCD_ 1。

SELECT CAST('1-1-2014 00:02:00'::timestamp at time zone 'America/Los_Angeles' AS Date) at time zone 'America/Los_Angeles';

返回

2014-01-01 00:00:00

但我希望它返回2013-12-31 00:00:00

我基本上想返回它所在时区的日期，在这种情况下是太平洋时区。

Answer 1

如果是没有时区的时间戳，您需要首先告诉它使用UTC时区，然后转换到另一个时区：

SELECT '1-1-2014 02:00:00'::timestamp at time zone 'UTC' at time zone 'America/Los_Angeles';
      timezone       
---------------------
 2013-12-31 18:00:00

Answer 2

如果'America/Los_Angeles'是您当前的时区（当前会话的timezone设置），那么只需告诉Postgres timestamp的时区即可。

SELECT ('2014-01-01 02:00:00'::timestamp AT TIME ZONE 'UTC')::date;

date的转换基于当前时区，并根据需要自动工作。

如果当前时区可以是其他时区，那么您需要像@Clodoaldo演示的那样明确。

正确的解决方案将首先存储一个timestamp with time zone（timestamptz），然后您可以直接转换为date:

SELECT '2014-01-01 02:00:00+0'::timestamptz::date;

关于Postgres:中的时间戳

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

最近的相关答案与更多细节（也涉及索引）：

• 优化/索引时区查询

Answer 3

我想好了，使用间隔，我可以减去偏移量，然后得到日期。

SELECT '1-1-2014 02:00:00'::timestamp + INTERVAL '1 hour' * extract(timezone_hour FROM '1-1-2014 00:02:00'::timestamp at time zone 'America/Los_Angeles');

在这里添加答案，因为我相信这个问题可能是其他人想知道的。