我有一个这样的转换表（简化）
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小时，则分配班次1，如果事件发生在23:20小时，则分配班次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到03:59之间的事件中检索ID = 3记录？
我试图在这个sqlfiddle
I have a turn shifts table like this (simplified)
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:59And I have alarm events (timestamps) that I need to check against a turn shift.
For example if the alarm event occurs at 14:34 Hrs then the shift 1 is assigned, if the event occurs at 23:20 Hrs then the shift 3 is assigned, and so on.
My first approach was like this:
SELECT id FROM shifts WHERE hr_start < '09:23:00':TIME AND hr_end > '09:23:00':TIME;And yes this works ok with ids 1,2 and 4, but not 3.
Then I attempt a BETWEEN query like this:
SELECT id FROM shifts WHERE '09:23:00':TIME BETWEEN r_start AND hr_end;With the same results
How I can retrieve the ID = 3 record for an event that occurs in the range of 22:00 Hrs to 03:59 Hrs?
My attempts in this sqlfiddle
最满意答案
在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以更轻松地测试查询。
Use CASE in WHERE condition:
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;Note. I have added CROSS JOIN to easier test the query.
如何在PostgreSQL中检查某个事件小时是否在一个小时的范围内？(How to check if a certain event hour is between a range of hours in PostgreSQL?)我有一个这样的转换表（简化）
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小时，则分配班次1，如果事件发生在23:20小时，则分配班次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到03:59之间的事件中检索ID = 3记录？
我试图在这个sqlfiddle
I have a turn shifts table like this (simplified)
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:59And I have alarm events (timestamps) that I need to check against a turn shift.
For example if the alarm event occurs at 14:34 Hrs then the shift 1 is assigned, if the event occurs at 23:20 Hrs then the shift 3 is assigned, and so on.
My first approach was like this:
SELECT id FROM shifts WHERE hr_start < '09:23:00':TIME AND hr_end > '09:23:00':TIME;And yes this works ok with ids 1,2 and 4, but not 3.
Then I attempt a BETWEEN query like this:
SELECT id FROM shifts WHERE '09:23:00':TIME BETWEEN r_start AND hr_end;With the same results
How I can retrieve the ID = 3 record for an event that occurs in the range of 22:00 Hrs to 03:59 Hrs?
My attempts in this sqlfiddle
最满意答案
在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以更轻松地测试查询。
Use CASE in WHERE condition:
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;Note. I have added CROSS JOIN to easier test the query.
发布评论