How to check if a specific time period exists between hours in PostgreSQL?
I have a shift 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:59
And I have alarming events (timestamps) that I need to check against rotation.
For example, if an alarm event occurs at 14:34 Hrs, then an offset of 1 is assigned, if an event occurs at 23:20 Hrs, then an offset of 3 is assigned, etc.
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 it works fine with IDs 1,2 and 4, but not 3.
Then I try to execute the BETWEEN request like this:
SELECT id
FROM shifts
WHERE '09:23:00':TIME BETWEEN r_start AND hr_end;
With the same results
How can I get the record ID = 3 for an event that occurs between 22:00 and 03:59 Hrs?
+3
source to share
1 answer
Use CASE in WHERE clause:
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've added a CROSS JOIN to make it easier to validate the request.
+3
source to share