postgresql 获取带有时区偏移量的日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22996959/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Getting date with timezone offset
提问by WallMobile
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.
我正在尝试从 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';
returns
回报
2014-01-01 02:00:00-08
-
——
SELECT CAST('1-1-2014 02:00:00'::timestamp at time zone 'America/Los_Angeles' AS Date);
returns
回报
2014-01-01
but I want it to return 2013-12-31.
但我希望它返回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';
returns
回报
2014-01-01 00:00:00
but I want it to return 2013-12-31 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.
我基本上想返回它所在时区的日期，在这种情况下是太平洋时区。
回答by Clodoaldo Neto
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:
如果它是没有时区的时间戳，您首先需要告诉它使用 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
回答by Erwin Brandstetter
If 'America/Los_Angeles'is your current time zone (timezonesetting of the current session) then just tell Postgres the time zone of the timestamp.
如果'America/Los_Angeles'是您当前的时区（timezone当前会话的设置），则只需告诉 Postgres timestamp.
SELECT ('2014-01-01 02:00:00'::timestamp AT TIME ZONE 'UTC')::date;
The cast to dateis based on the current time zone and works automatically as desired.
转换到date基于当前时区并根据需要自动工作。
If the current time zone can be something else, you need to be explicit like @Clodoaldo demonstrates.
如果当前时区可以是其他时区，则需要像 @Clodoaldo 演示一样明确。
The proper solutionwould be to store a timestamp with time zone(timestamptz) to begin with, then you can just cast to date:
将妥善解决将是存储timestamp with time zone（timestamptz）开始说起，那么你可以转换为date：
SELECT '2014-01-01 02:00:00+0'::timestamptz::date;
About timestamps in Postgres:
关于 Postgres 中的时间戳：
Recent related answer with more details (also dealing with indexes):
最近有更多细节的相关答案（也处理索引）：
回答by WallMobile
I figured it out, using the interval, I can subtract the offset and then get the date.
我想通了，使用间隔，我可以减去偏移量，然后得到日期。
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');
Adding the answer here, since I am sure this question might be something others would want to know.
在此处添加答案，因为我确定这个问题可能是其他人想知道的。
