在选择计划的项目时，请在Postgres中考虑DST

Question

我有 postgres 时钟警报的表（不是真的，但这是类似的，更易于解释）。警报由用户设置1小时的分辨率，用户可以来自许多不同的时区。警报每天都在重复。我想可靠地获取应该在一天中的特定小时发出的警报，并且我在日光节省时间方面遇到问题。我该如何以最好的方式做到这一点？

示例

阿尔弗雷德（Alfred）和洛塔（Lotta）都住在斯德哥尔摩（UTC 1小时，但 2H当DST时）。
Sharon居住在新加坡（UTC 8小时，没有DST）

在冬季，阿尔弗雷德（Alfred）设置了一个警报，持续4点。警报应该响起
夏季，洛塔（Lotta）设置了警报凌晨5点。同样，它应该全年凌晨5点。
同时，沙龙设置了一个警报，为上午11点。

所有这些都可以存储在数据库中，为03:00 UTC。

如果我在冬季查询数据库是否是否应在03:00 UTC，我想要Alfred和Sharon的警报。新加坡现在 7H从瑞典开始，新加坡上午11点在瑞典凌晨4点。洛塔的警报不应该再去一个小时。

相反，如果我在夏天查询数据库中是否询问警报应该在03:00 UTC下车，我想要Lotta和Sharon的警报。新加坡现在是瑞典的 6小时，所以新加坡上午11点是凌晨5点瑞典现在。Sven的警报在一个小时前发出。

我如何存储此信息并查询数据库？

我可以在必要时更改DB模式。目前，我们根本没有调整DST，实际上只有"小时"。整数字段（看起来很愚蠢，时间字段会更好）。

看来我需要同时存储UTC时间和时区信息，但是我不知道如何在Postgres中获得最佳实现。我发现Postgres具有某种时区的概念，但据我所知，没有时区域字段类型。另外，我想我需要在SQL中进行一些计算，以确定如何根据时区数据和创建日期来抵消选择中的UTC时间。我对SQL不好…

我确实想在Postgres中解决这个问题，因为"警报"可能会有很多，并且我想避免将所有这些都取得到Ruby并在此处进行过滤所带来的性能问题。（是的，这是一个铁路应用程序。）

Answer 1

使用timestamp with time zone（timestamptz）进行计算。
警报的时间可以是time [without time zone]。
但是您必须为每行明确保存时区。

从不使用time with time zone（timetz）这是一种逻辑上的类型，其使用被PostgreSQL抑制。手册：

time with time zone类型由SQL标准定义，但是定义展示了导致可疑实用性的属性。在大多数情况下，date，time，timestamp without timezone和timestamp with time zone的组合应提供完整任何应用程序所需的日期/时间功能范围。

演示设置：

CREATE TABLE alarm(name text, t time, tz text);
INSERT INTO alarm VALUES
  ('Alfred', '04:00', 'Europe/Stockholm') -- Alfred sets an alarm for 4 AM.
, ('Lotta',  '05:00', 'Europe/Stockholm') -- Lotta sets an alarm for 5 AM. 
, ('Sharon', '11:00', 'Asia/Singapore');  -- Sharon has set an alarm for 11 AM.

必须是时区名称（不是缩写）才能说明DST。相关：

• 将属性相同的时区名称应用于时间戳
时产生不同的结果

获取"今日"的匹配警报：

SELECT *
FROM   alarm
WHERE  (('2012-07-01'::date + t) AT TIME ZONE tz AT TIME ZONE 'UTC')::time
       = '03:00'::time

• ('2012-7-1'::date + t) ...组装timestamp [without time zone]也可以是 now()::date + t "今天"。
• AT WITH TIME ZONE tz ...将时间戳放在保存的时区，导致timestamptz。
• AT WITH TIME ZONE 'UTC' ...根据UTC timestamp
获取
• ::time ...提取时间组件的最简单方法。

在这里您可以查找时区名称：

SELECT *
FROM   pg_timezone_names
WHERE  name ~~* '%sing%'
LIMIT  10;

db＆lt;＆gt; fiddle - 演示夏季/冬季
_{旧的SQLFIDDLE}

Answer 2

您将使用全日制区域名称，例如美国/new_york而不是EDT/EST，并将小时存储在该时区而不是UTC。然后，您可以非常不知道日光节省的偏移更改。

类似以下内容应该有效：

-- CREATE TABLE time_test (
--   user_to_alert CHARACTER VARYING (30),
--   alarm_hour TIME,
--   user_timezone CHARACTER VARYING (30)
-- );
SELECT user_to_alert, 
  CASE
    WHEN EXTRACT(HOUR FROM CURRENT_TIME AT TIME ZONE user_timezone) = EXTRACT(HOUR FROM alarm_hour) THEN TRUE
  ELSE FALSE
END AS raise_alarm
FROM time_test;

或：

SELECT user_to_alert
FROM time_test
WHERE EXTRACT(HOUR FROM CURRENT_TIME AT TIME ZONE user_timezone) = EXTRACT(HOUR FROM alarm_hour);

Answer 3

给定：

SET timezone = 'UTC';
CREATE TABLE tzdemo (
    username text not null,
    alarm_time_utc time not null,
    alarm_tz_abbrev text not null,
    alarm_tz text not null
);
INSERT INTO tzdemo (username, alarm_time_utc, alarm_tz_abbrev, alarm_tz) VALUES
('Alfred', TIME '04:00' AT TIME ZONE '+01:00', 'CET', 'Europe/Stockholm'),
('Lotta', TIME '05:00' AT TIME ZONE '+02:00', 'CEST', 'Europe/Stockholm'),
('Sharon', TIME '11:00' AT TIME ZONE '+08:00', 'SGT', 'Singapore');

尝试：

SELECT username 
FROM tzdemo 
WHERE alarm_time_utc AT TIME ZONE alarm_tz_abbrev = TIME '03:00' AT TIME ZONE alarm_tz;

结果：

 username 
----------
 Alfred
 Sharon
(2 rows)

原理：

• 存储时区抵消了警报的偏移，包括当时是dst
• 还存储转换为UTC的时钟时间
• 查询时，请使用以下事实：全时区名称遵循当前的UTC规则，以产生该区域的 Current 时区域中的时间。与在创建警报时的时区域的存储时间戳进行比较。

这还使您可以应对用户更改位置的情况，从而更改时区。

当您想进行预测性查询时，可以通过日期征服时间戳来扩展此方法，例如"当地时间在哪些地方会警告位置的声音"。

我对此解决方案并不完全自信，并建议您进行仔细的测试。