0

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?

2
Contributors
3
Replies
12
Views
1 Year
Discussion Span
Last Post by pritaeas
0

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.

0

The query I currently have is this:

SELECT 
        SUM(TIMESTAMPDIFF(MINUTE, StartDateTime, FinishDateTime)) As Duration,
        monthname(StartDateTime) As Month
    FROM Entries
    WHERE 
        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?

0

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.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.