CODEWITHSUNDEEP
mysqlsqldatebetween
jQuerybeast
jQuerybeast

Reputation: 14490

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

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:

  1. Everything that starts between 2018-03-05 and 2018-03-11
  2. Everything that is due between 2018-03-05 and 2018-03-11
  3. 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'))

Upvotes: 3

Views: 434

Answers (2)

Richard Hansell
Richard Hansell

Reputation: 5403

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?

Upvotes: 1

giorgiga
giorgiga

Reputation: 1778

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.

Upvotes: 2

Related Questions