Postgres：根据日期、时间和时区名称查询计划。

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运行，我需要获取所有day_of_week中包含星期一(where days_of_week @> ARRAY[1])并且hours_of_day为09:00 UTC的日程。这是由于hours_of_day存储为整数数组，但我们也存储了用户的时区。

因此，用户可能会说："在星期一早上9点给我发送一条消息"(我们将其存储为[9])，但这意味着他们所在的时区是早上9点。

问题：

• 是否有任何方法可以查询所有符合这些参数的日程表？
• 如果没有，是否有更好的方法来结构化数据以确保通过Postgres更轻松地进行查询？架构是灵活的。

提前致谢！

- jmccartie

---

您的意思是要在第3天的8点、4月13日和5月22日发送消息，还是要在所有天数的8-13-22小时发送消息，包括第3-4-5天？ - Patrick

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 可能对时区名称和缩写非常挑剔，并且其夏令时的行为可能令人费解：例如，太平洋标准时间和太平洋夏令时间被视为两个不同的时区（对于 AT TIME ZONE 的目的）。

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数据库中找到。Postgres表pg_timezone_names和pg_timezone_abbrevs也可能会引起兴趣。

- uhoh-itsmaciek

---

非常出色且详细的回答。继续保持！ - Patrick