postgresql - Postgres tsrange，按日期和时间过滤

Question

我有一个 events 表，其中有一个名为 duration 的字段，其类型为 tsrange，用于捕获事件的开始和结束时间这是timestamp类型。我想要的是能够过滤特定日期范围内的所有事件，然后按时间过滤这些事件。例如，用户应该能够过滤在(含)12-15-2019 到 12-17-2019 之间发生且在晚上 9 点播放的所有事件。为此，用户提交一个日期范围来过滤该日期范围内的所有事件:

WHERE lower(duration)::date <@ '[start, finish]'::daterange

上面的start和finish是用户提交的参数。

然后我想按特定时间段内播放的事件来过滤这些事件，例如晚上 9 点，基本上只显示开始时间和结束时间之间有晚上 9 点的事件。

如果我有下表:

id    duration
---  ------------------------------------
 A    2019-12-21 19:00...2019-12-22 01:00
 B    2019-12-17 16:00...2019-12-17 18:00
 C    2019-12-23 19:00...2019-12-23 21:00
 D    2019-12-23 19:00...2019-12-24 01:00
 E    2019-12-27 14:00...2019-12-27 16:00

并且用户提交2019-12-21到2019-12-27的日期范围，则事件B将被过滤掉。然后用户提交时间为晚上 9:00 (21:00)，此时将返回 A、C 和 D。

编辑

我能够使用以下方法让它工作:

WHERE duration @> (lower(duration)::date || ' 21:00:00')::timestamp

上面的21:00是用户数据，但看起来有点hackish

Answer 1

tsrange 包含晚上 9 点的时间戳。当且仅当晚上 9 点开始当天或晚上 9 点第二天是范围的一部分。

你可以用它来写你的条件。

一个例子:

lower(r)::date + TIME '21:00' <@ r OR
(lower(r)::date + 1) + TIME '21:00' <@ r

测试 r 是否包含晚上 9 点的时间戳。