PostgreSQL UTC to CET/CEST

Question

我不太懂时区和富兰克林。

我有一个带有UTC时间戳字段的表，没有存储时区。

我的目标是按星期或按片(如n小时)对该表的行进行分组。

我一直到现在都是这样的:

-- CET  => UTC/GMT + 1 => Winter
-- CEST => UTC/GMT + 2 => Summer
--
-- Hour changes 2014 : march 30th, 2h CET => 3h CEST
--
--
--      Output for "d"             Wanted output for "d"
-- "2014-03-28 23:00:00+00"   "2014-03-28 23:00:00+00" (23h UTC because CET)
-- "2014-03-29 23:00:00+00"   "2014-03-29 23:00:00+00" (23h UTC because CET)
-- "2014-03-30 23:00:00+00"   "2014-03-30 22:00:00+00" (22h UTC because CEST : fail because start timestamp is CET)
SELECT dates.d AS d, archives_d, range_begin_date, sequentialid
  FROM generate_series('2014-03-28T23:00:00+00:00'::timestamp, -- 23h UTC because CET -- 2014-03-28T22:00:00+00:00
                       '2014-03-31T21:59:59+00:00'::timestamp, -- 22h UTC because CEST -- 2014-03-31T21:59:59+00:00
                       '86400 seconds') AS dates(d)
  LEFT JOIN (
     -- Select archive date floored by a step (here 86400 seconds -> 24 hours)
     SELECT *, (to_timestamp(((floor(extract(epoch from (archives.range_begin_date - '2014-03-28T23:00:00+00:00'::timestamp)) / 86400)) * 86400) + extract(epoch from '2014-03-28T23:00:00+00:00'::timestamp))) AS archives_d
     FROM archives
  ) AS archives
    ON dates.d = archives.archives_d
 ORDER BY dates.d

您知道如何获得所需的输出(至少对于生成系列)吗?

请注意，我的生成序列的步长不是固定在一天，而是一个任意的间隔。

谢谢

Answer 1

在pg_timezone_names中有一个完整的时区名称和偏移量列表。

我猜你想要一个基于城市的名字，而不是基于区域的名字。这样你就可以得到夏令时的调整。

=> SELECT now() AT TIME ZONE 'America/New_York' AS ny, now() AT TIME ZONE 'Europe/London' AS lon;
            ny             |            lon
---------------------------+---------------------------
 2014-08-04 09:01:06.08988 | 2014-08-04 14:01:06.08988
(1 row)

以上是在13:01 UTC发布的。我猜纽约是你想要的，但是地理不是我的强项:-)