PostgreSQL - 如何在不同的时区渲染日期? [英] PostgreSQL - how to render date in different time zone?
问题描述
例如,我想渲染 2012-05-29 15:00:00
as 2012-05-29 16:00:00 EDT
。
实现它?
to_char('2012-05-29 15:00:00'::时区'EST5EDT'的时间戳,'YYYY -MM-DD HH24:MI:SS TZ')
给出 2012-05-29 16:00:00
(无区域) p>
to_char('2012-05-29 15:00:00'::时区戳'EST5EDT','YYYY-MM-DD HH24 :MI:SS TZ')
给出 2012-05-29 14:00:00 CDT
(错误)
这是一个很好的工作,但它是非常复杂的,必须有一个更简单的方法: replace(replace(to_char(('2012-05-29 15:00:00' :时区EST5EDT的时间戳):: timestamptz,'YYYY-MM-DD HH24:MI:SS TZ','CST','EST'),'CDT','EDT')
关键是将本地时区切换到des有效的显示时区,在交易期间:
begin;
将本地时区设置为EST5EDT;
选择to_char('2012-05-29 15:00:00'::时区在时区'CDT',
'YYYY-MM-DD HH24:MI:SS TZ');
结束
结果是:
2012-05-29 16:00:00 EDT
请注意,使用 set [本地]时区
需要使用全时区名称而不是缩写(例如,CST将无法使用)。在 pg_timezone_names
视图中查找有效的选择。
要在类似于to_char的上下文中使用该方法( )调用,我相信这个功能可以完成这个工作:
CREATE FUNCTION display_in_other_tz(
in_t timestamptz,
in_tzname文本,
in_fmt文本)RETURNS文本
AS $$
DECLARE
v文本;
save_tz文字;
BEGIN
将时区转换为save_tz;
EXECUTE'将本地时区设置为'|| quote_literal(in_tzname);
SELECT to_char(in_t,in_fmt)INTO v;
EXECUTE'将本地时区设置为'|| quote_literal(save_tz);
返回v;
END;
$$ language plpgsql;
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:00
as 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'::timestamp at time zone 'EST5EDT', 'YYYY-MM-DD HH24:MI:SS TZ')
gives 2012-05-29 14:00:00 CDT
(wrong).
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')
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
Note that with set [local] timezone
it is required to use full time zone names instead of abbreviations (for instance, CST would not work). Look up in the pg_timezone_names
view for valid choices.
To use that method in a context similar to a to_char() call, I believe this function does the job:
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;
这篇关于PostgreSQL - 如何在不同的时区渲染日期?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!