postgresql - 如何检查某个事件时间是否在 PostgreSQL 的时间范围内？

Question

我有一个像这样的轮类表(简化版)

 id | hr_start |  hr_end
----+----------+----------
  1 | 08:00:00 | 15:59:59
  2 | 16:00:00 | 21:59:59
  3 | 22:00:00 | 03:59:59
  4 | 04:00:00 | 07:59:59

而且我有警报事件(时间戳)需要检查轮类。

例如，如果警报事件发生在 14:34 Hrs，则分配类次 1，如果事件发生在 23:20 Hrs，则分配类次 3，依此类推。

我的第一个方法是这样的:

SELECT id 
FROM shifts 
WHERE hr_start < '09:23:00':TIME AND hr_end > '09:23:00':TIME;

是的，这适用于 ID 1,2 和 4，但不适用于 3。

然后我尝试这样的 BETWEEN 查询:

SELECT id 
FROM shifts 
WHERE '09:23:00':TIME BETWEEN r_start AND hr_end;

同样的结果

如何检索发生在 22:00 Hrs 到 03:59 Hrs 范围内的事件的 ID = 3 记录？

My attempts in this sqlfiddle

Answer 1

在 WHERE 条件下使用 CASE:

SELECT id 
FROM shifts
CROSS JOIN (SELECT '23:00:00'::TIME AS event) sub
WHERE 
    CASE WHEN hr_start <= hr_end THEN hr_start <= event AND hr_end >= event
    ELSE hr_start <= event OR hr_end >= event END;

注意。我添加了 CROSS JOIN 以更轻松地测试查询。