postgresql - 将时区添加到 PostgreSQL 中的时间戳(不转换为该时区)

Question

我有 3 个类型为 VARCHAR 的独立字段(包含时区，例如“欧洲/罗马”)、DATE 和 INTERVAL。我想在查询中组合它们，以便获得 TIMESTAMP WITH TIME ZONE。这个取值的规则是:

给定时区指定日期的正午，负12小时加上给定的时间间隔(处理DST需要这个-12小时)

显然加/减 INTERVAL 不是问题。我的问题是我不明白如何创建“给定时区指定日期的中午”。

一个简单的例子:

• 如果我有时区字段“欧洲/罗马”
• 日期为 2018-03-24(该时区夏令时转换日期)
• 间隔为“1 小时”

计算应该是这样的:

1. 创建指定日期的中午:“欧洲/罗马”时区的 2018-03-24 12:00:00
2. 减去 INTERVAL“12 小时”，结果是 2018-03-24 01:00:00 在“欧洲/罗马”时区(因为 DST)
3. 添加 INTERVAL '1 hour'，最终结果为 2018-03-24 02:00:00 at 'Europe/Rome' time zone。

我该怎么做第 1 点？

附言我无法更改数据模式。来自GTFS加载到 postgres 中的数据。简而言之，这个模式的特点是它在 3 个不同的表中存储时区、日期和时间间隔:agency , calendar_dates和 stop_times (好吧，时区可能在其他表中，但这对于这个问题并不重要)。

Answer 1

您的示例在语法上不正确，但您的问题似乎是 PostgreSQL 将其中包含日期的字符串文字解释为 timestamp with time zone。

但你必须走另一条路:你想要中午十二点作为timestamp without timezone，然后使用AT TIME ZONE 'Europe/Rome' 来获得绝对时间戳(在 PostgreSQL 中称为 timestamp with time zone)包含“noon as it is in Rome”，然后加上小时。

显示的结果将取决于您的 session 时区。

让我们使用 UTC 以便我们得到相同的结果并在 SQL 中编写上面的内容:

SET timezone=UTC;

SELECT '2018-03-24 12:00:00'::timestamp without time zone
   AT TIME ZONE 'Europe/Rome'
   + INTERVAL '1 hour';

        ?column?        
------------------------
 2018-03-24 12:00:00+00
(1 row)