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?

Add the MONTH() part to your select clause, so you can use an IF() to determine your calculation when the start and finish month is different.

The query I currently have is this:

        SUM(TIMESTAMPDIFF(MINUTE, StartDateTime, FinishDateTime)) As Duration,
        monthname(StartDateTime) As Month
    FROM Entries
        YEAR(StartDateTime) = YEAR(FinishDateTime) 
        AND MONTH(StartDateTime) = MONTH(FinishDateTime)
        AND StartDateTime BETWEEN '2016-08-05 00:00:00' AND '2016-09-05 23:59:59' 
        AND FinishDateTime BETWEEN '2016-08-05 00:00:00' AND '2016-09-05 23:59:59'
    GROUP BY MONTH(StartDateTime)

Which gives me the hours in the month where the start and end are within the same month/year. Where/How would I add the IF() into this?

You need to split your duration into three columns, the first the same as now, the second and third should be filled when the months differ between start and finish. In the second calculate from start to end of day, the third should be start of next day till finish.

Then use your new columns to create the correct sum.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.18 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.