获取带有时区偏移的日期 [英] Getting date with timezone offset

问题描述

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

I am trying to extract the date from a query in postgres. The timestamp is stored as UTC, so if I have 1/1/2014 02:00:00, I want the date in pacific time, to be 12/31/2013, not 1/1/2014. I am really close, but both query 2 and 3 still return 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

我基本上想返回所在时区的日期

I basically want to return the date in the timezone it is in, in this case the pacific timezone.

推荐答案

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

If it is timestamp without time zone you need first to tell it to use the UTC time zone and then convert to another time zone:

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

这篇关于获取带有时区偏移的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！