选择计划项目时在 Postgres 中考虑 DST [英] Accounting for DST in Postgres, when selecting scheduled items

问题描述

我有一个 Postgres 表，其中包含 时钟闹钟(不是真的，但这是类似的，并且更容易解释).警报由用户设置，分辨率为 1 小时，用户可以来自许多不同的时区.警报每天都在重复.我想可靠地获取应该在一天中的特定时间响起的警报，但我遇到了夏令时问题.我该如何以最好的方式做到这一点?

I have a Postgres table of clock alarms (not really, but this is analogous, and easier to explain). Alarms are set by users with a 1 hour resolution, and users can be from many different timezones. The alarms are repeating daily. I want to reliably fetch the alarms that are supposed to go off at a particular hour of the day, and I am having problems with daylight saving time. How do I do this in the best way?

Alfred 和 Lotta 都住在斯德哥尔摩(距 UTC +1 小时，但 +2h当它是 DST 时).
Sharon 住在新加坡(距 UTC +8 小时，没有夏令时)

Alfred and Lotta both live in Stockholm (+1 hour from UTC, but +2h when it's DST).
Sharon lives in Singapore (+8 hours from UTC, no DST)

在冬天，阿尔弗雷德设置了凌晨 4 点的闹钟.警报应该响起全年当地时间凌晨 4 点.
在夏季，洛塔会设置闹钟早上 5 点.同样，它应该全年在早上 5 点开始.
与此同时，Sharon 已经设置了上午 11 点的闹钟.

During winter, Alfred sets an alarm for 4 AM. The alarm should go off at 4 AM local time, all year.
During summer, Lotta sets an alarm for 5 AM. Again, it should go off at 5 AM all year round.
Meanwhile, Sharon has set an alarm for 11 AM.

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

All of these can be stored in the database as 03:00 UTC.

如果我在冬天查询数据库以获取应该在03:00 UTC，我想要 Alfred 和 Sharon 的闹钟.新加坡现在是+7h来自瑞典，所以新加坡的上午 11 点是瑞典的凌晨 4 点.洛塔的警报再过一个小时就不会再响了.

If I query the database in the winter for alarms that should go off at 03:00 UTC, I want Alfred's and Sharon's alarms. Singapore is now +7h from Sweden, so 11 AM in Singapore is 4 AM in Sweden. Lotta's alarm should not go off for another hour.

相反，如果我在夏天查询数据库中的警报，应该在 03:00 UTC 响起，我想要 Lotta 和 Sharon 的闹钟.新加坡现在距离瑞典 +6h，所以新加坡的上午 11 点是上午 5 点现在的瑞典.斯文的闹钟在一小时前响了.

Conversely, if I query the database in the summer for alarms that should go off at 03:00 UTC, I want Lotta's and Sharon's alarms. Singapore is +6h from Sweden now, so 11 AM in Singapore is 5 AM in Sweden now. Sven's alarm went off an hour ago.

如何存储并查询数据库?

如有必要，我可以更改数据库架构.目前，我们根本不针对 DST 进行调整，实际上只有一个小时"时间.整数字段(看起来很笨，时间字段会更好).

I can change the db schema if necessary. At the moment, we don't adjust for DST at all, and in fact just have an "hour" integer field (which seems dumb, a time field would be better).

似乎我需要同时存储 UTC 时间和时区信息，但我不知道如何在 Postgres 中最好地实现这一点.我发现 Postgres 有某种时区概念，但据我所知没有时区字段类型.另外，我想我需要在 SQL 中进行一些计算，以确定如何根据时区数据和创建日期在选择中偏移 UTC 时间.我不太擅长 SQL……

It seems I need to store both a UTC time and timezone information, but I don't know how to best achieve this in Postgres. I've found that Postgres has some sort of concept of timezones, but no timezone field type as far as I can tell. Also, I guess I need to do some calculations in SQL to determine how to offset the UTC time in the select, based on the timezone data and the creation date. I'm not great with SQL…

我确实想在 Postgres 中解决这个问题，因为可能会有很多警报"，并且我想避免将所有警报都提取到 Ruby 中并在那里进行过滤所带来的性能问题.(是的，这是一个 Rails 应用程序.)

I do want to solve this in Postgres, as there can be a lot of "alarms", and I want to avoid the performance issues that come with fetching all of them into Ruby and filter there. (Yes, this is a Rails app.)

推荐答案

使用 timestamp with time zone (timestamptz) 进行计算.
闹钟时间可以是time [无时区].
但是您必须为每一行明确保存时区.

Use timestamp with time zone (timestamptz) for calculations.
Times for alarms can be time [without time zone].
But you have to save the time zone explicitly for every row.

从不使用 time with time zone (timetz) 这是一个逻辑错误的类型，PostgreSQL 不鼓励使用它.手册:

Never use time with time zone (timetz) It's a logically broken type, its use is discouraged by PostgreSQL. The manual:

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

The type time with time zone is defined by the SQL standard, but the definition exhibits properties which lead to questionable usefulness. In most cases, a combination of date, time, timestamp without timezone, and timestamp with time zone should provide a complete range of date/time functionality required by any application.

演示设置:

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.相关:

It has to be time zone names (not abbreviations) to account for DST. Related:

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

获取今天"的匹配警报:

Get matching alarms for "today":

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) ... assemble timestamp [无时区]也可以是 now()::date + t 表示今天".
• AT WITH TIME ZONE tz ... 将时间戳放在保存的时区，从而产生 timestamptz.
• AT WITH TIME ZONE 'UTC' ... 根据 UTC 获取 timestamp
• ::time ... 提取时间分量的最简单方法.
• ('2012-7-1'::date + t) ... assemble timestamp [without time zone] Could also just be now()::date + t for "today".
• AT WITH TIME ZONE tz ... place timestamp at the saved time zone, resulting in timestamptz.
• AT WITH TIME ZONE 'UTC' ... get according UTC timestamp
• ::time ... simplest way to extract the time component.

您可以在此处查找时区名称:

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

db<>小提琴这里 -展示夏天/冬天
_{旧版sqlfiddle}

这篇关于选择计划项目时在 Postgres 中考虑 DST的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！