algorithm - 在数据库中，如何存储事件发生日期和时间范围以进行快速/优雅查询？

Question

假设我正在维护一个事件数据库，它可以是从企业营业时间的每周时间表(周一至周五:上午 10 点至晚上 7 点，周六:中午 12 点至下午 6 点，周日:休息)到每月事件 (艺术博览会，每个第一个星期六，上午 10 点至下午 5 点)到年度事件(平安夜慈善晚宴，晚上 7 点至晚上 9 点)

在理想情况下，我想在任何一天查询数据库，比如:2012 年 1 月 12 日，星期四，中午 12 点

...并查找发生在以下时间的所有事件:

• 常规的星期四中午(例如营业时间)
• 每个第二个星期四(艺术博览会)
• 一月。 12，特别是(呃……土库曼斯坦阵亡将士纪念日)

我想如果不首先考虑如何将此类日期/时间存储在数据库中，就没有必要讨论查询构造。

我想不出如何在单条记录、单个字段中对每周正常工作时间(甚至忽略边缘情况)的概念进行建模，同时对每年一次的事件进行建模。至少，我似乎至少需要五个字段:

• 开始时间
• 持续时间
• 星期几(例如周一、周二等)
• 可选的绝对年度日期(例如 5 月 8 日)
• 可选的每月一次(第四个星期三)

而且我猜没有办法将其封装在一行中，对吧？例如，一家每个工作日营业的企业将有 5 条记录。

最终目标是能够进行一个相对优雅的查询，该查询可以找到在其时间范围内包含给定时刻的所有事件记录。

Answer 1

不确定您是否专门要求与 dbms 无关的解决方案，无论如何在 postgresql 中(我认为在大多数 RDBMS 中都是可能的)您可以存储时间戳并从中获取大量信息:http://www.postgresql.org/docs/8.4/interactive/functions-datetime.html .

Postgresql 特定的答案

9.9.1. EXTRACT, date_part

EXTRACT(field FROM source) The extract function retrieves subfields such as year or hour from date/time values. source must be a value expression of type timestamp, time, or interval. (Expressions of type date are cast to timestamp and can therefore be used as well.) field is an identifier or string that selects what field to extract from the source value. The extract function returns values of type double precision. The following are valid field names: [...] day, DOW (day of week), DOY (day of year), [...]

例如，要选择每个第二个星期四发生的事件，您可以这样写:

SELECT * FROM events  #your table
    WHERE EXTRACT(DOW FROM date_column)=4 # DOW  goes from sunday (0) to saturday (6)
    AND EXTRACT(DAY FROM date_column)>7 # must occurr after 7th day of the month (ie. if the 1st is a Thursday
    AND EXTRACT(DAY FROM date_column)<15 # and before the 15th (ie. if the 1st is a Friday, so the first Thursday is on the 7th, and the second is on the 14th)

要为持续时间建模，您可能只有 2 个时间戳字段，用于事件的开始/结束。

另请注意，您可以添加或减去时间戳，以及了解时间间隔是否重叠。

一般，我会首先尝试使用您的 DBMS 提供的数据类型和函数，只有当您真的找不到解决方案时才尝试自己建模。