title: How to calculate time outside of work hours
body: <p>This seemed pretty straight forward initially, but has proved to be a real headache. Below is my table, data, expected output and SQL Fiddle of where I have got to in solving my problem.</p>
  
  <p><strong>Schema &amp; Data:</strong></p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS `meetings` (
    `id` int(6) unsigned NOT NULL,
    `user_id` int(6) NOT NULL,
    `start_time` DATETIME,
    `end_time` DATETIME,
    PRIMARY KEY (`id`)
  ) DEFAULT CHARSET=utf8;
  INSERT INTO `meetings` (`id`, `user_id`, `start_time`, `end_time`) VALUES
    ('0', '1', '2018-05-09 04:30:00', '2018-05-09 17:30:00'),
    ('1', '1', '2018-05-10 06:30:00', '2018-05-10 17:30:00'),
    ('2', '1', '2018-05-10 12:30:00', '2018-05-10 16:00:00'),
    ('3', '1', '2018-05-11 17:00:00', '2018-05-12 11:00:00'),
    ('4', '2', '2018-05-11 07:00:00', '2018-05-12 11:00:00'),
    ('5', '2', '2018-05-11 04:30:00', '2018-05-11 15:00:00');
  </code></pre>
  
  <p>What I would like to get from the above is total time worked outside of 09:00 to 17:00, grouped by day and user_id. So the result from the above data would look like:</p>
  
  <pre><code>  date        | user_id | overtime_hours
    ---------------------------------------
    2018-05-09  | 1       | 05:00:00
    2018-05-10  | 1       | 03:00:00
    2018-05-11  | 1       | 07:00:00
    2018-05-12  | 1       | 09:00:00
    2018-05-11  | 2       | 13:30:00
    2018-05-12  | 2       | 09:00:00
  </code></pre>
  
  <p>As you can see the expected results are only summing overtime for each day and user for those hours outside of 9 to 5.</p>
  
  <p>Below is the query and SQL Fiddle of where I am. The main issue comes when the start and ends straddle midnight (or multiple midnight's)</p>
  
  <pre><code>SELECT
      SEC_TO_TIME(SUM(TIME_TO_SEC(TIME(end_time)) - TIME_TO_SEC(TIME(start_time)))), user_id, DATE(start_time)
  FROM
  (SELECT 
      start_time, CASE WHEN TIME(end_time) &gt; '09:00:00' THEN DATE_ADD(DATE(end_time), INTERVAL 9 HOUR) ELSE end_time END AS end_time, user_id
  FROM
      meetings
  WHERE
      TIME(start_time) &lt; '09:00:00'
  
  UNION
  
  SELECT 
      CASE WHEN TIME(start_time) &lt; '17:00:00' THEN DATE_ADD(DATE(start_time), INTERVAL 17 HOUR) ELSE start_time END AS start_time, end_time, user_id
  FROM
      meetings
  WHERE
      TIME(end_time) &gt; '17:00:00') AS clamped_times
  GROUP BY user_id, DATE(start_time)
  </code></pre>
  
  <p><a href="http://sqlfiddle.com/#!9/77bc85/1" rel="nofollow noreferrer">http://sqlfiddle.com/#!9/77bc85/1</a></p>
  
  <p>Pastebin for when the fiddle decides to flake: <a href="https://pastebin.com/1YvLaKbT" rel="nofollow noreferrer">https://pastebin.com/1YvLaKbT</a></p>
  
  <p>As you can see the query grabs the easy overtime with start and ends on the same day, but does not work with the multiple day ones.</p>