SQL finding overlapping of times pass midnight (across 2 days)

Question

Hi everyone,

I know there are lots of these types of questions, but i didn't see one that was similar enough to my criteria. So i'd like to ask for your help please. The fields i have are just start and end which are of time types. I cannot involve any specific dates in this. If the time ranges don't go pass midnight across day, i'd just compare two tuples as such:

end1 > start2 AND start1 < end2

(end points touching are not considered overlapped here.)

But when I involve time range that pass (or at) midnight, this obviously doesn't work. For example, given:

 start  |   end
--------+--------
06:00PM | 01:00AM
03:00PM | 09:00PM

Without involving dates, how can i achieve this, please. My assumption is, if end is less than start, then we're involving 2 days.

I'm trying to do this in plain standard SQL, so just a simple and concise logic in the WHERE clause.

Thank you everyone!

---

Added:

Also, how would I test if one time range completely envelopes another? thanks again!

Answer 1

use start time and duration (in minutes or whatever unit is appropriate)

Answer 2

If your SQL supports time differences:

(end1 - start1) > (start2 - start1) AND (end2 - start2) > (start1 - start2)

Answer 3

Unfortunately, "plain" SQL will be too general to use against an actual database. The reason is that the various database products have different levels of support for calculating the duration between two times. For example, in SQL Server 2008, it would be substantially simpler to convert the time values to DateTime and then do the comparison since many comparison operators are not supported on the Time data type.

Select ...
From    (
        Select Cast(T.Start1 As DateTime) As Start1
            , Case
                When Cast(T.Start1 As DateTime) > Cast(T.End1 As DateTime) Then DateAdd(d,1,Cast(T.End1 As DateTime))
                Else Cast(T.End1 As DateTime)
                End As End1
        From ...
        ) As T
Where T.End1 > T2.Start2 And T1.Start2 < T2.End2            

Answer 4

The program Transtar had this problem. The time data was not associated with any date, nor was the time data in a date time field. The program initially was designed to issue transit itinaries from about 4AM to midnight which worked fine as long as the transit wasn't around the clock. I built a function which did a sliding test for the times so that if you asked for 5AM it would look at times from 1AM to 12:59AM. I wrote it in FORTRAN, but the algorythm would be the same regardless of language.