Intersecting/overlapping dates in MySQL

I recently tried to solve a problem of checking if a certain user was available for work within a certain time period based on other entries in a table containing start and end dates.

When searching online I didn’t find quite as much information as I had hoped, however found a very useful illustration that helped me visualize the problem more easily

 

From this i wrote the following SQL code

 

 

Another interesting problem I had is that in my case, the table did not contain the end_date column, however contained a duration column, which measured the duration of a certain job in days, from this we can use some MySQL Function magic to create a column alias that will be the end_date:

 

When doing this we have to use HAVING instead of WHERE because WHERE can not perform “queries” on column aliases.

If you get the following error:

Try disabling strict mode, if you are using laravel, you have to edit your mysql driver in config/database.php  like this:

I hope that helps the next poor soul that have a similar problem.

 

2 comments

  1. You may want to take a closer look at the logic. Lines 8, 9 and 10 in both queries have no effect. In line 8 you say start date has to be both before April 1st and after May 1st at the same time. That is never true. Similarly for end date in line 9 and start date again in line 10. The only constrain that matters is line 7. That makes sense if you go back to the (a, b) an (x, y) illustration. There is overlap as long as a x.

    Regarding MySQL: Please don’t use HAVING without a GROUP BY. Just put the date expression into the where clause. Assuming ‘a’ is start_date, ‘b’ is end_date (that is start_date + duration), ‘x’ is ‘2017-04-01’ and ‘y’ is ‘2017-05-01’ it goes something like this:

    WHERE start_date = (‘2017-04-01’ – INTERVAL duration DAY)

    By moving the interval to the right side on ‘>=’ (and subtracting instead of adding) we make it easier for MySQL to deduce how it can use an index on start_date to narrow down the number of rows efficiently.

Leave a Reply

Your email address will not be published. Required fields are marked *