PostgreSQL - 如何在不同的时区中呈现日期？

Question

PostgreSQL - 如何在不同的时区中呈现日期？

31

我的服务器在中央时间。我想使用东部时间呈现时间戳。

例如，我想将2012-05-29 15:00:00呈现为2012-05-29 16:00:00 EDT。

我该如何实现？

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')返回的结果是 2012-05-29 14:00:00 CDT (错误)。

下面这个方法虽然可行，但过于复杂了，应该有更简单的方法：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')

- Konrad Garus

服务器如何知道您想显示哪个时区缩写？它只知道时区偏移量。 - Erwin Brandstetter

1

时区信息并不是黑魔法。从技术上讲，没有任何理由使得在东部时间中说5/29是夏令时变得不可能（甚至困难）。事实上，PG知道所有这些 - to_char(x, 'TZ') 可以正确地区分CST和CDT，而 at time zone EST5EDT 也能正确地处理夏令时。我唯一缺少的是使用这些信息进行优雅的渲染。 - Konrad Garus

1

这里的问题在于Postgres开发人员奇怪地选择了timestamp with time zone at _zone_应该转换时区，但返回timestamp without time zone。 - lanzz

2

@lanzz 周围有很多奇怪的地方可以将 timestamp <-> timestamptz 转换并使用 at time zone，但是真的没有内置的方法来呈现不同的时区，比如 fmt_date(x, 'YYYY-MM-DD HH24:MI:SS TZ', 'EST5EDT') 吗？ - Konrad Garus

我曾经在过去的某个时候思考过同样的问题，就我所记得的而言，并没有找到一个明智的解决方案。最好的做法可能是在应用程序侧面管理时区和格式，而不是在数据库中进行处理，尽管这并不是你问题的真正答案。 - lanzz

@lanzz，虽然令人失望，但“不可能”也是一个有效的答案。 - Konrad Garus

2个回答

4

实际上，PG知道一切 - to_char(x，'TZ')可以正确区分CST和CDT，并且在时区EST5EDT中也考虑了夏令时。处理时间戳时，Postgres知道以下内容：

GUC timezone的设置。
数据类型。
值，对于timestamp和timestamptz，它是自“1970-1-1 0:0 UTC”以来的秒数（或者更精确地说：UT1）。
有关其他时区的详细信息，请参见日期/时间配置文件。

当解释输入时，Postgres使用提供的时区信息。在呈现时间戳值时，Postgres使用timezone设置的当前值，但是时区偏移量、缩写或名称仅用于计算正确的值。它们不会被保存。后来无法提取这些信息是不可能的。更多细节请参见相关答案：

你的“正确”示例几乎是正确的。 to_char()的TZ返回'CDT'用于中央时间的夏令时期间的时间戳，否则返回'CST'。东部时间(EST/EDT)在相同的本地时间切换夏令时。引用维基百科：

时间调整为当地时间上午2:00。

两个时区每年有两小时的时间是不同步的。当然，这永远不会影响到15:00或16:00的时间戳，只会影响到02:00左右的时间。一个完全正确的解决方案 - 就像@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;

SET LOCAL 的影响仅在当前事务结束之前持续。

关于 SET LOCAL 的手册。

- Erwin Brandstetter

这不是我要找的。我问的是日期渲染，而不是转换。我知道如何转换为“时间戳”，我要找的是一种在一般情况下以不同时区名称呈现“varchar”的方法。 - Konrad Garus

@KonradGarus：今天早些时候我没有更多时间。我现在认为我更清楚地理解了你的意图，也知道为什么你的解决方案几乎是正确的，但又不完全正确。 - Erwin Brandstetter

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Daniel Vérité · Accepted Answer

关键是在交易期间将本地时区切换为所需的显示时区：

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;

结果为：

2012-05-29 16:00:00 EDT

请注意，如果使用 set [local] timezone 方法，则需要使用完整的时区名称而不是缩写（例如，CST将无法正常工作）。可以在 pg_timezone_names 视图中查找有效的选择。

我认为要在类似于 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;