How to check if a time span overlaps other time spans

Question

I am stuck with the question how to check if a time span overlaps other time spans in the database.

For example, my table looks like this:

id start    end
1  11:00:00 13:00:00
2  14:30:00 16:00:00

Now I try to make a query that checks if a timespan overlaps with one of these timespans (and it should return any time spans that overlaps).

When I try 14:00:00 - 15:00:00 it should return the second row.
When I try 13:30:00 - 14:15:00 it shouldn't return anything.
When I try 10:00:00 - 15:00:00 it should return both rows.

It's hard for me to explain, but I hope someone understands me enough to help me.

It's not necessary to put tag info (like "(SQL)") in the subject of your question; that's why we have **tags**. :-) — Ken White, Dec 20 '13 at 17:32
Please remember that tables have names, and quote the name in the question. — Jonathan Leffler, Dec 20 '13 at 17:42

score 6 · Accepted Answer · answered Dec 20 '13 at 17:22

When checking time span overlaps, all you need is a query like this (replace @Start and @End with your values):

For non-overlaps

SELECT * 
FROM tbl
WHERE @End < tbl.start OR @Start > tbl.end

Thus, reversing the logic, for overlaps

SELECT *
FROM tbl
WHERE @End >= tbl.start AND @Start <= tbl.end

score 0 · Answer 2 · answered Dec 20 '13 at 17:20

0

Assuming your timespan is denoted by two values - lower_bound and upper_bound, just make sure they both fall between the start and end:

SELECT *
FROM   my_table
WHERE  start <= :lower_bound AND end >= :upper_bound

answered Dec 20 '13 at 17:20

Mureinik

297,002
52
306
350

@KenWhite This query checks if `[:lower_boud, :upper_bound]` is contained in `[start, end]`, which is my understanding of what the OP requested. If the requirement is the oposite, then yes, indeed, the signs should be reversed. – Mureinik Dec 20 '13 at 17:23
I think you might be right, after re-reading the question. It's not phrased really clearly. :-) – Ken White Dec 20 '13 at 17:30

score 0 · Answer 3 · answered Dec 20 '13 at 18:08

Here is the SQL fiddle http://sqlfiddle.com/#!2/e4aee/37

You can also use BETWEEN operator, comparatively this should be faster than using three logical expressions in WHERE clause

SELECT * 
FROM
(
  SELECT CONCAT(CAST(HOUR(TD) AS CHAR(2)), ':', CAST(MINUTE(TD) AS CHAR(2)), ':', CAST(SECOND(TD) AS CHAR(2)))  AS DT1
  FROM DUMMY
) A 
WHERE DT1 BETWEEN '14:03:02' AND '18:01:01'

How to check if a time span overlaps other time spans

3 Answers3