MySQL – Calculating Hours in a Month per Date Range

MySQL

I have a table in mysql with the following data:

| StartDateTime       | FinishDateTime |
| 2016-08-18 10:00:00 | 2016-08-18 11:00:00 |
| 2016-08-18 12:00:00 | 2016-08-18 14:00:00 |
| 2016-08-31 17:00:00 | 2016-09-01 09:00:00 |

What I would like to get as the output is

| Month               | Duration       |
| Aug                 | 10             |
| Sept                | 9              |

I can get the right output when each record is only within 1 month but I am not sure how to go about crossing month boundaries. Can anyone point me in the right direction?

Best Answer

Create a months table:

CREATE TABLE Months (
  MonthDate date
);

and populate it with dates like these:

2016-07-01
2016-08-01
2016-09-01
...

Then you can get your results with a query like this:

SELECT
  m.MonthDate,
  SUM(TIMESTAMPDIFF(HOUR,
                    GREATEST(m.MonthDate                    , t.StartDateTime ),
                    LEAST   (m.MonthDate + INTERVAL 1 MONTHS, t.FinishDateTime)
                   )) AS Duration
FROM
  YourTable AS t
  INNER JOIN Months AS m ON t.StartDateTime  < m.MonthDate + INTERVAL 1 MONTHS
                        AND t.FinishDateTime > m.MonthDate
GROUP BY
  m.MonthDate
;

The output would be in the form:

MonthDate   Duration
----------  --------
2016-08-01  10
2016-09-01  9

Changing the dates to month names would, in my view, constitute a presentation issue but, if necessary, MySQL offers built-in functions like MONTHNAME to do the transformation directly in SQL.

Additional information can be found in the MySQL manual: