CODEWITHSUNDEEP
sqlsql-servert-sql
M_S_N
M_S_N

Reputation: 2810

sql: filter datetime columns based on given intervals

I am facing an issue related to filtering out events that were active during a certain time interval i.e. 7 to 9

EventId             StartTime                       EndTime            
18   2013-04-11 16:33:11.735342100   2013-04-11 17:16:47.976164100  
19   2013-04-10 16:33:46.575337300   2013-04-11 18:10:08.428443900  
20   2013-04-10 17:17:04.033083300   2013-04-11 18:10:13.907757900  
21   2013-04-11 00:10:24.293352100   2013-04-11 18:45:17.754240800  
23   2013-04-11 01:11:20.278558900   2013-04-11 18:45:00.435247300  
25   2013-04-15 09:42:22.549026700   2013-04-15 23:54:33.389964300  
26   2013-04-16 07:42:24.588807700   2013-04-16 17:42:41.077751300  
28   2013-04-19 16:51:22.699240800   2013-04-19 18:39:03.167468100  
31   2013-04-19 18:30:56.891621300   2013-04-19 19:42:50.418640200  
17   2013-04-20 16:07:07.327879000   2013-04-20 22:17:17.783053600

I want to filter out those events that were active during 7-9am including events that start at 8, I used simple time comparison and it works as intended, but given the data-set it fails to get some of the results e.g event Id 20

20   2018-05-10 17:17:04.033083300   2018-05-11 18:10:13.907757900

as this event is active in the time interval 7-9 next day, but i am unable to get it, because of the date change.

Upvotes: 2

Views: 73

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

I think this logic detects any overlap:

where -- more than one day, then automatic
      endTime > dateadd(day, 1, startTime) or
      ( -- same day
       convert(date, endTime) = convert(date, startTime) and
       datepart(hour, startTime) < 9 and
       datepart(hour, endTime >= 7
      ) or
      ( -- next day
       convert(date, endTime) = dateadd(day, 1, convert(date, startTime)) and
       datepart(hour, startTime) < 9 or
       datepart(hour, endTime >= 7
      )

Note that this detects any overlap with the time period, not a full overlap.

EDIT:

For a full overlap:

where -- more than one day, then automatic
      endTime > dateadd(day, 1, startTime) or
      ( -- same day
       convert(date, endTime) = convert(date, startTime) and
       convert(time, startTime) <= '07:00:00' and
       convert(time, endTime) >= '09:00:00'
      ) or
      ( -- next day
       convert(time, startTime) <= '07:00:00' or
       convert(time, endTime) >= '09:00:00'
      )

Upvotes: 3

Related Questions