postgresql - Postgres : query schedule given day, time_of_day，和时区名称

Question

我正在编写一个“此时向我发送消息”应用程序。我以这种方式存储重复信息:

Schedules
----------
days_of_week: [3, 4, 5]
hours_of_day: [8, 13, 22]
time_zone: "Pacific Time (US & Canada)"

在显示方面效果很好，但我需要编写一个频繁的 cron 作业来获取“现在 (utc)”的所有时间表。因此，如果 cron 作业在星期一 09:00 UTC 运行，我需要获取所有计划，其中

• 星期一在 days_of_week 中(where days_of_week @> ARRAY[1])
• hours_of_day 为 09:00 UTC。假设 hours_of_day 存储为整数数组，但我们还存储了用户的时区。

所以用户可能会说:“在星期一上午 9 点给我发消息”(我们将其存储为 [9])，但这意味着 他们 时区的上午 9 点。

问题:

• 有什么方法可以查询给定这些参数的所有时间表？
• 如果没有，是否有更好的方法来构建数据以确保更容易通过 Postgres 进行查询？该架构非常灵活。

提前致谢!

Answer 1

Postgres 具有处理时区的一流工具，我已经使用 AT TIME ZONE 编写了与您在此询问的内容非常相似的内容。构造。除了您的字段之外，我还使用 last_scheduled_at 来标记最后一次“执行”计划的时间——即最后一次成功的 cron 作业何时为该计划运行以避免双重计划，以及 deleted_at 用于计划的逻辑删除。

除了我只有一个小时之外，我的时间表架构很相似。我像你一样将天数存储在一个数组中，并将时区存储为 text。我的 schedules 表中的字段是 dows、hour 和 timezone。

这是查询:

SELECT
  s.*
FROM
  schedules s
WHERE
  ARRAY[extract(dow from timestamptz (now() at time zone timezone))] && dows
    AND hour = extract(hour from timestamptz (now() at time zone timezone))
    AND (s.last_scheduled_at IS NULL
          OR s.last_scheduled_at < (now() - interval '12 hours'))
    AND s.deleted_at IS NULL
LIMIT
  1000

我使用 &&(重叠)而不是 @>(包含)，但两者都有效。您可能还需要限制，以便您可以分批处理工作(继续运行它，如果结果为零，您将完成 X 小时；确保在小时结束之前完成)。您可能还想将时间戳作为参数传递给此查询——我在这里将其内联为 now() 以简化操作，但将时间作为参数传递会使测试成为一个容易多了。

另请注意，Postgres 可能对时区名称和缩写很挑剔，它的夏令时行为可能违反直觉:例如，太平洋标准时间和太平洋夏令时被视为两个不同的时区(出于 在时区):

maciek=# select now() at time zone 'pst';
          timezone          
----------------------------
 2015-10-09 23:14:51.856813
(1 row)

maciek=# select now() at time zone 'pdt';
          timezone          
----------------------------
 2015-10-10 00:14:54.402524
(1 row)

也就是说，夏令时始终存在，无论您当前是否在观察它。如果您让人们直接输入时区，最好拒绝这些或自动将它们强制为“America/Los_Angeles”(或他们碰巧映射到的任何时区)，这将根据以下内容自动为您处理这些转换您的 Postgres 版本具有的时区规则(如果对于经常更改时区的区域，准确性至关重要，请确保您及时更新到点发布)。 Postgres 使用的时区名称列表可以在 Olson database 中找到。 . Postgres 表 pg_timezone_names 和 pg_timezone_abbrevs也可能感兴趣。