CODEWITHSUNDEEP
sqlt-sql
user1381745
user1381745

Reputation: 3900

SQL mental block - checking if one period of time overlaps another

I have four unix times. Two are the beginning and end of "today" (whichever that day may be) - i.e. 00:00:00 and 23:59:59 - and the other two are user-chosen times.

What I need is a (TSQL-friendly) query to check if the period between "today" times overlaps with the user's times. Can anyone help?

Upvotes: 2

Views: 2888

Answers (3)

Robert K
Robert K

Reputation: 150

I know this post is old but I think the accepted answer is incomplete.There most be at least one more intersect case and modify the above.

INTERSECT (FIXED)

Date Range 1   |         |>----------------------<|
Date Range 2   |                 |>------------------------<|
WHERE Range1Start <= Range2Start AND Range1End <= Range2End AND Range1End>=Range2Start

Fixing like this allows to avoid detect Range 2 in Range 1 as an Intersection (which is the query result in the accepted solution)

The next two are OK but are not well illustrated

Range 1 in Range 2

Date Range 1   |                      |>-------------<|
Date Range 2   |                 |>------------------------<|

Date Range 1   |                 |>------------------------<|
Date Range 2   |                 |>------------------------<|

WHERE Range1Start >= Range2Start AND Range1End <= Range2End

Range 2 in Range 1:

Date Range 1   |         |>------------------------<|
Date Range 2   |               |>-------------<|

Date Range 1   |         |>------------------------<|
Date Range 2   |         |>------------------------<|


WHERE Range2Start >= Range1Start AND Range2End <= Range1End

And finally the second intersect

INTERSECT (RIGHT)

Date Range 1   |                |>----------------------<|
Date Range 2   |       |>------------------------<|
WHERE Range2Start <= Range1Start AND Range2End <= Range1End AND Range1Start <=Range2End

Depending on what you need I would suggest not using greater than (>=) and less than (<=) 'cause you also asume that equals period are in these groups but is up to you

EQUALS RANGES

Date Range 1   |         |>------------------------<|
Date Range 2   |         |>------------------------<|


WHERE Range1Start = Range2Start AND Range1End = Range2End

Upvotes: 0

bendataclear
bendataclear

Reputation: 3850

I have this in a txt doc to remember (date dyslexia):

Intersects:

Date Range 1   |         |>----------------------<|
Date Range 2   |                 |>------------------------<|

where Range1Start <= Range2End and Range1End >= Range2Start

Range 1 in Range 2:

Date Range 1   |                   |>-------------<|
Date Range 2   |                 |>------------------------<|


where Range1Start >= Range2Start and Range1End <= Range2End

Range 2 in Range 1:

Date Range 1   |         |>----------------------<|
Date Range 2   |                 |>-------------<|


where Range2Start >= Range1Start and Range2End <= Range1End

Upvotes: 5

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

where UserStart <= TodayEnd and
      UserEnd >= TodayStart

Upvotes: 3

Related Questions