Hours in a month per date range 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?
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  &lt; m.MonthDate + INTERVAL 1 MONTHS
                        AND t.FinishDateTime &gt; 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:


TIMESTAMPDIFF()
MONTHNAME()
GREATEST()
LEAST()