SQL: Select * where start and end falls within two dates

Question

I have a tasks table as such:

+-------+-------------------+---------------------+---------------------+
| rowid | title             | start_task          | due_date            |
+-------+-------------------+---------------------+---------------------+
| 1     | Paint Apartment   | 2018-03-01 07:00:00 | 2018-03-16 15:00:00 |
+-------+-------------------+---------------------+---------------------+
| 2     | Meeting with John | 2018-03-10 10:00:00 | 2018-03-10 12:30:00 |
+-------+-------------------+---------------------+---------------------+
| 3     | Buy Milk          | 2018-03-04 20:00:00 | NULL                |
+-------+-------------------+---------------------+---------------------+
| 4     | Renew Rail Card   | NULL                | 2018-03-07 09:00:00 |
+-------+-------------------+---------------------+---------------------+

And I want to select everything for the week 2018-03-05 to 2018-03-11 so it should print rows 1,2 & 4.

So my query should include the following:

Everything that starts between 2018-03-05 and 2018-03-11
Everything that is due between 2018-03-05 and 2018-03-11
Everything that the period of start_task and due_date falls within 2018-03-05 and 2018-03-11

My current SQL that does 1 and 2 and prints rows 2 & 4:

SELECT * FROM `tasks` tasks where ((`start_task` >= '2018-03-05 00:00:00' and `start_task` <= '2018-03-11 23:59:59') OR (`due_date` >= '2018-03-05 00:00:00' and `due_date` <= '2018-03-11 23:59:59'))

@barmar The answers in the duplicated question you marked does not answer my question. In 2 minutes you havent even read my question and you marked it as duplicated. — jQuerybeast, Mar 06 '18 at 09:37
I thought you wanted to check if the range between start_task and due_date includes the range 2018-03-05 to 2018-03-11. — Barmar, Mar 06 '18 at 09:37
@Barmar You're assigning duplicated questions based on your first thought without even reading the content? — jQuerybeast, Mar 06 '18 at 09:39
Can you give an example of something that meets criteria 3 but not 1 or 2? — Barmar, Mar 06 '18 at 09:39
I read the question. Your description is not very clear, but I thought I understood it to be the same as the other. I've reopened, OK? — Barmar, Mar 06 '18 at 09:39

giorgiga · Answer 1 · 2018-03-06T10:47:37.233

You are checking for a non-empty intersection between two intervals (start_date ➔ due_date and Mar 05 ➔ Mar 11).

You can do this with:

select *
  from `tasks` tasks
 where (`start_task` is null or `start_task` <= '2018-03-11 23:59:59') -- start <= end
   and (`due_date`   is null or `due_date`   >= '2018-03-05 00:00:00') -- end   >= start

(assuming null start/end date means unbounded interval)

Remember it as: "each interval must start before the other ends".

To better understand why, think of the inverse problem.

In order for two intervals to have an empty intersection you must either have interval 2 end before interval 1 starts:

interval 1: --------(---)--------
interval 2: -(---)---------------

or interval 2 start after interval 1 ends:

interval 1: --------(---)--------
interval 2: ---------------(---)-

Negate the conjunction of these two conditions and apply De Morgan to get a condition for non-empty intersection.

Richard Hansell · Accepted Answer · 2018-03-06T11:30:17.397

So you are just missing anything where the start - due date period spans the week you are checking? This should do the trick:

SELECT 
    * 
FROM 
    `tasks` tasks 
WHERE 
    (
        (`start_task` >= '2018-03-05 00:00:00' and `start_task` <= '2018-03-11 23:59:59') 
        OR (`due_date` >= '2018-03-05 00:00:00' and `due_date` <= '2018-03-11 23:59:59')
        OR (`start_task` <= '2018-03-11 23:59:59' and `due_date` >= '2018-03-05 00:00:00')
    )

As has been mentioned this can also be reduced to a single test:

SELECT 
    * 
FROM 
    `tasks` tasks 
WHERE
    COALESCE(`start_task`, '2018-03-11 23:59:59') <= '2018-03-11 23:59:59' 
    AND COALESCE(`due_date`, '2018-03-05 00:00:00') >= '2018-03-05 00:00:00'

I think that's the right syntax for MySQL, apologies if it isn't. Note that this would also mean that a NULL start date AND a NULL due date would be a pass, when you might want that to be a fail?

Event A can be said to overlap Event B if Event A starts before Event B ends, and ends after Event B starts. That's the only comparison that needs to be considered. The rest is noise. — Strawberry, Mar 06 '18 at 10:44
Apart from the fact that NULLs are involved. I was going to add an edit to show how this could be reduced to a single test using COALESCE to handle the NULL cases, but my MySQL isn't fantastic... — Richard Hansell, Mar 06 '18 at 11:23

SQL: Select * where start and end falls within two dates

2 Answers2