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

SELECT
    start_date,
   end_date
FROM table
WHERE (
     (`start_date` <= '2017-05-01' AND `end_date` >= '2017-04-01')
  OR (`start_date` >= '2017-05-01' AND `start_date` <= '2017-04-01' AND `end_date` <= '2017-04-01')
  OR (`end_date` <= '2017-04-01' AND `end_date` >= '2017-05-01' AND `start_date` <= '2017-05-01')
  OR (start_date >= '2017-05-01' AND start_date <= '2017-04-01')
)

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.

SELECT
    start_date,
   DATE_ADD(start_date, INTERVAL duration DAY) AS end_date
FROM jobs
HAVING (
      (`start_date` <= '2017-05-01' AND `end_date` >= '2017-04-01')
   OR (`start_date` >= '2017-05-01' AND `start_date` <= '2017-04-01' AND `end_date` <= '2017-04-01')
   OR (`end_date` <= '2017-04-01' AND `end_date` >= '2017-05-01' AND `start_date` <= '2017-05-01')
   OR (start_date >= '2017-05-01' AND start_date <= '2017-04-01')
);

If you get the following error:

Non-grouping field 'start_date' is used in HAVING clause

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

'mysql' => [
    'driver' => 'mysql',
    'host' => env('DB_HOST', '127.0.0.1'),
    'port' => env('DB_PORT', '3306'),
    'database' => env('DB_DATABASE', 'forge'),
    'username' => env('DB_USERNAME', 'forge'),
    'password' => env('DB_PASSWORD', ''),
    'charset' => 'utf8',
    'collation' => 'utf8_unicode_ci',
    'prefix' => '',
    'strict' => true,
    'engine' => null,
    'modes' => [  // <- Add this
        'STRICT_TRANS_TABLES',
        'NO_ZERO_IN_DATE',
        'NO_ZERO_DATE',
        'ERROR_FOR_DIVISION_BY_ZERO',
        'NO_AUTO_CREATE_USER',
        'NO_ENGINE_SUBSTITUTION'
    ]
],

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