PostgreSQL - 如何在不同时区呈现日期？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10797720/
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
PostgreSQL - how to render date in different time zone?
提问by Konrad Garus
My server is in Central Time. I would like to render timestamps using Eastern time.
我的服务器在中部时间。我想使用东部时间呈现时间戳。
For instance, I would like to render 2012-05-29 15:00:00as 2012-05-29 16:00:00 EDT.
例如，我想渲染2012-05-29 15:00:00为2012-05-29 16:00:00 EDT.
How can I achieve it?
我怎样才能实现它？
to_char('2012-05-29 15:00:00'::timestamptz at time zone 'EST5EDT', 'YYYY-MM-DD HH24:MI:SS TZ')gives 2012-05-29 16:00:00(no zone).
to_char('2012-05-29 15:00:00'::timestamptz at time zone 'EST5EDT', 'YYYY-MM-DD HH24:MI:SS TZ')给出2012-05-29 16:00:00（无区域）。
to_char('2012-05-29 15:00:00'::timestamp at time zone 'EST5EDT', 'YYYY-MM-DD HH24:MI:SS TZ')gives 2012-05-29 14:00:00 CDT(wrong).
to_char('2012-05-29 15:00:00'::timestamp at time zone 'EST5EDT', 'YYYY-MM-DD HH24:MI:SS TZ')给2012-05-29 14:00:00 CDT（错误）。
This one works, but it's so ridiculously complicated there must be an easier way: replace(replace(to_char(('2012-05-29 15:00:00'::timestamptz at time zone 'EST5EDT')::timestamptz, 'YYYY-MM-DD HH24:MI:SS TZ'), 'CST', 'EST'), 'CDT', 'EDT')
这个有效，但它是如此复杂，必须有一个更简单的方法： replace(replace(to_char(('2012-05-29 15:00:00'::timestamptz at time zone 'EST5EDT')::timestamptz, 'YYYY-MM-DD HH24:MI:SS TZ'), 'CST', 'EST'), 'CDT', 'EDT')
回答by Daniel Vérité
The key is to switch the local timezone to the desired display timezone, for the duration of the transaction:
关键是在交易期间将本地时区切换到所需的显示时区：
begin;
set local timezone to 'EST5EDT';
select to_char('2012-05-29 15:00:00'::timestamp at time zone 'CDT',
'YYYY-MM-DD HH24:MI:SS TZ');
end;
The result is:
结果是：
2012-05-29 16:00:00 EDT
2012-05-29 16:00:00 美国东部时间
Note that with set [local] timezoneit is required to use full time zone names instead of abbreviations (for instance, CST would not work). Look up in the pg_timezone_namesview for valid choices.
请注意，set [local] timezone它需要使用完整的时区名称而不是缩写（例如，CST 不起作用）。在pg_timezone_names视图中查找有效的选择。
To use that method in a context similar to a to_char() call, I believe this function does the job:
要在类似于 to_char() 调用的上下文中使用该方法，我相信这个函数可以完成这项工作：
CREATE FUNCTION display_in_other_tz(
in_t timestamptz,
in_tzname text,
in_fmt text) RETURNS text
AS $$
DECLARE
v text;
save_tz text;
BEGIN
SHOW timezone into save_tz;
EXECUTE 'SET local timezone to ' || quote_literal(in_tzname);
SELECT to_char(in_t, in_fmt) INTO v;
EXECUTE 'SET local timezone to ' || quote_literal(save_tz);
RETURN v;
END;
$$ language plpgsql;
回答by Erwin Brandstetter
In fact, PG knows it all - to_char(x, 'TZ') differentiates CST from CDT correctly, and at time zone EST5EDT respects DST as well.
事实上，PG 知道这一切 - to_char(x, 'TZ') 正确区分 CST 和 CDT，并且在时区 EST5EDT 也尊重 DST。
When dealing with a timestamp Postgres knows:
在处理时间戳时 Postgres 知道：
- The setting of the GUC
timezone. - The data type.
- The value, which is the same count of seconds since '1970-1-1 0:0 UTC' for
timestampandtimestamptz. (Or, to be precise: UT1.) - Details about other time zones in your date/time configuration files
- GUC 的设置
timezone。 - 该数据类型。
- 的值，这是因为秒相同的计数“1970-1-1 0：0 UTC”的
timestamp和timestamptz。（或者，准确地说：UT1。） - 有关日期/时间配置文件中其他时区的详细信息
When interpreting input, Postgres uses information about the provided time zone.
When rendering a timestamp value, Postgres uses the currenttimezonesetting, but time zone offset, abbreviationor nameare only used to compute the correct valueon input. They are not saved. It is impossibleto extract that information later. More details in this related answer:
在解释输入时，Postgres 使用有关提供的时区的信息。渲染时间戳值时，Postgres 使用当前timezone设置，但时区偏移、缩写或名称仅用于计算输入的正确值。他们没有得救。这是不可能的后提取的信息。此相关答案中的更多详细信息：
Your "correct" example is almostcorrect. TZof to_char()returns 'CDT' for timestamps that fall in the daylight saving periods of Central Time and 'CST' else. Eastern Time (EST/EDT) switches daylight saving hours at the same localtime - I quote Wikipedia:
您的“正确”示例几乎是正确的。TZofto_char()返回 'CDT' 对于属于中部时间夏令时和 'CST' 其他时间的时间戳。东部时间 ( EST/ EDT) 在同一当地时间切换夏令时 - 我引用维基百科：
The time is adjusted at 2:00 AM local time.
时间调整为当地时间凌晨 2:00。
The two time zones are out of syncduring two hours per year. Of course, this can never affect a timestamp at 15:00or 16:00, only around 02:00.
两个时区每年有两个小时不同步。当然，这永远不会影响15:00或的时间戳16:00，只会影响02:00。
A fully correct solution - much like what @Daniel already posted, slightly simplified:
一个完全正确的解决方案 - 很像@Daniel 已经发布的内容，稍微简化了：
BEGIN;
SET LOCAL timezone to 'EST5EDT';
SELECT to_char('2012-05-29 15:00 CST6CDT'::timestamptz
, 'YYYY-MM-DD HH24:MI:SS TZ')
RESET timezone; -- only if more commands follow in this transactions
END;
The effects of SET LOCAL last only till the end of the current transaction.
SET LOCAL 的效果只持续到当前事务结束。
