获取带时区偏移量的日期

Question

我试图从Postgres的查询中提取日期。时间戳存储为UTC，因此如果我有1/1/2014 02:00:00，我想要太平洋时间的日期是12/31/2013，而不是1/1/2014。我已经非常接近了，但是第2和第3个查询仍然返回1/1/2014。

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

但我希望它返回2013-12-31。

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

我基本上想要以它所在的时区返回日期，这里是太平洋时区。

- WallMobile

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

- Clodoaldo Neto

---

我会给你正确的答案。这比我的解决方案更简洁、更清晰。谢谢！ - WallMobile

如果您的客户端时区与服务器时区不同，请小心使用此解决方案。时间戳值将隐式分配给您的客户端时区，然后按照所写的方式转换为“UTC”。为避免这种情况，请将第一个转换重写为::timestamp without time zone at time zone 'UTC'。 - Corbin

Answer 2

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

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

将数据类型转换为date是基于当前时区的，并会自动按照预期方式工作。

如果当前时区可能是其他值，则需要明确指定，@Clodoaldo演示了如何做到这一点。

正确的解决方案是一开始就存储带有时区的时间戳timestamptz，然后你可以直接将其转换为date：

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

关于Postgres中的时间戳：

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

最近的相关答案，提供更多细节（还涉及索引）：

• 优化/索引时区查询

- Erwin Brandstetter

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');

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

- WallMobile

---

这不考虑夏令时。 - frankhommers