Subdivide timestamps into 3 timeslots

Question

I have following problem where I have to group different transactions into timeslots. Suppose you have a table with records which contain an entry datetimestamp. These records are created by users (operators) who work in different shifts

Shift 1: 5 - 13h // Shift 2: 13 - 21h // Shift 3: 21 - 5h

Now I want to have a flexible query which rounds the timestamps down to the start time of the shift. Example:

2010-09-08 06:12:00.000 --> 2010-09-08 05:00:00.000
2010-09-08 02:12:00.000 --> 2010-09-07 21:00:00.000

I already tried a few queries using dateadd and datediff but I don't get it to work... Can anybody help? Thanks

Answer 1

How about this...?

select 
    case
        when datepart(Hh, dt) >= 5 AND datepart(Hh, dt) < 13 then 1
        when datepart(Hh, dt) >= 13 AND datepart(Hh, dt) < 21 then 2
        when datepart(Hh, dt) < 5 OR datepart(Hh, dt) >= 21 then 3
    end
from myTable

Answer 2

select
case 
  when datepart(hh, start_date) between 5 and 12
       then dateadd(hh, 5, dateadd(d, datediff(d, 0, start_date), 0))
  when datepart(hh, start_date) between 13 and 20
       then dateadd(hh, 13, dateadd(d, datediff(d, 0, start_date), 0))
  when datepart(hh, start_date) between 21 and 23
       then dateadd(hh, 21, dateadd(d, datediff(d, 0, start_date), 0))
  else dateadd(hh, 21, dateadd(d, datediff(d, 0, start_date)-1, 0))
end
from ...

Answer 3

select 
    case
        --shift I   
        when datepart(HH, [TimeStamp]) >= 5 and datepart(HH, [TimeStamp]) < 13 then
            dateadd(HH, 5, dateadd(dd,0, datediff(dd,0,[TimeStamp])))
        --shift II
        when datepart(HH, [TimeStamp]) >= 13 and datepart(HH, [TimeStamp]) < 21 then
            dateadd(HH, 13, dateadd(dd,0, datediff(dd,0,[TimeStamp])))
        --shift III
        when datepart(HH, [TimeStamp]) >= 21 then               
            dateadd(HH, 21, dateadd(dd,0, datediff(dd,0,[TimeStamp])))
        when datepart(HH, [TimeStamp]) < 5 then         
            dateadd(HH, 21, dateadd(dd,0, datediff(dd,0,[TimeStamp])-1))                            
    end as StartTime
from 
    Table1

Answer 4

If your database supports an INTERVAL type, subtract five hours from the event's timestamp to line it up relative to midnight instead of 0500 and divide the time part by eight hours to get a shift number indexed from zero.

To get the shift start time, multiply the shift number by eight hours, add that to the date part of the subtraction you did above and then add five additional hours to line it back up with your shift schedule.

If you're going to be querying these a lot, you might be better off to create a second table that individually identifies each shift by an ID and its start time. You can then populate the shifts table using an ON INSERT trigger on the table containing your events that does the calculation once, inserts a new row into the table of shifts if needed and ties your event row to it using a foreign key. That would also give you a much easier way to query all of the events that happened during a specific shift.