-
Any given row timestamp can only be assigned to a single shift. In a CASE statement like shown, the first match wins.
-
You are using
<=for your shift ending times. That is likely incorrect, as a row exactly on a boundary matches two conditions. You should use half-open intervals.
Side note: This query will run very, very slowly when you get a bunch of rows, because you are converting your timestamps to strings to do your comparisons. This prevents efficient use of an index on your timestamps. Instead, convert your constant strings, with dates, to true timestamps, and compare as timestamps.