跨时区查询

Question

我正在开发一个应用程序，用户可以要求在他们的时区每天的特定时间发送电子邮件给他们。例如，用户A住在伦敦，在伦敦时间每天下午2点发送一封电子邮件，用户B住在纽约，在纽约时间下午2点发送一封电子邮件。

我想知道我应该如何配置我的数据库postgres，以便调度器可以每分钟触发一次，并查询在该分钟发送的所有电子邮件，而不管他们在哪个时区。

我想避免的一件事是必须运行多个查询，每个时区一次。

Answer 1

由于世界各地的日光节约时间(DST)规则(坦率地说，相当愚蠢)，本地时间可以用绝对(UTC时间)表示各种事情。

保存 time (不是timetz !)和时区名称(不是缩写)用于何时发送电子邮件。这个相关问题下的棘手细节:
具有相同属性的时区名称在应用于时间戳

时会产生不同的结果

CREATE TABLE event (
   event_id serial PRIMARY KEY
 , alarm_time time  -- local alarm time
 , tz text          -- time zone name
 , ...
);

使用下面的表达式"烹饪"精确的每日时间点，同时考虑到本地夏令时设置:

SELECT current_date + alarm_time AT TIME ZONE tz;

的例子:

SELECT current_date + '2:30'::time AT TIME ZONE 'Europe/London' AS alarm_ts

的回报:

alarm_ts
2014-05-19 02:30:00+02

在整个应用程序中使用timestamp with time zone (timestamptz)。一定要了解它是如何工作的。这篇全面的文章可能会有所帮助(也解释了AT TIME ZONE的构造:
在Rails和PostgreSQL中完全忽略时区

只是为了清楚，一旦你"煮熟"了每天的UTC时间，你可以把它翻译成任何当地时间，也可以使用它。但是，用UTC完成其余的操作可能会少一些混乱。