How to calculate difference between two timestamps and then group by month for charting purposes in SQL

Question

I'm trying to form a SQL statement that meets the following criteria based on records that have date/timestamps and various other varchar and int fields.

• Calculate the difference between two fields named time_depart and time_return in decimal hours
• Total up the differences and group by month up to the last 12 months.
• Separate totals based on event_type

Example Data Set

table events:

id | time_depart         | time_return         | event_type  | summary
--------------------------------------------------------------
1  | 2019-02-11 10:00:00 | 2019-02-11 10:30:00 | 1           | test summary
2  | 2019-02-11 10:30:00 | 2019-02-11 11:30:00 | 1           | some more data
3  | 2019-02-11 11:00:00 | 2019-02-11 12:30:00 | 2           | even more data
4  | 2019-02-11 11:30:00 | 2019-02-11 13:30:00 | 2           | just a summary
5  | 2019-02-11 12:00:00 | 2019-02-11 14:30:00 | 2           | again more
......

Would like a return similar to where event_type = 1 is 'training' and event_type = 2 is a 'mission'

month     | trainingTime | missionTime  
--------------------------------------------------------------
january   | 15.25        | 22 
february  | 20           | 25
march     | 10.5         | 35.5
april     | 52           | 20 
may       | 64           | 72 
june      | 100          | 10.75 
july      | 45           | 0 
august    | 26           | 15
september | 10.5         | 65
october   | 55           | 8
november  | 44           | 12.25 
december  | 17           | 0

Answer 1

I have just searched this on another link:

SELECT TIMESTAMPDIFF(MONTH, time_depart, time_return) as difference;

I hope this helped... link: The difference in months between dates in MySQL

Answer 2

Easy enough.

SELECT
MONTH(time_return) as month,
SUM(TIMESTAMPDIFF(SECOND, time_depart, time_return)) as sum_diff_in_seconds,
TIME_FORMAT(SUM(SEC_TO_TIME(TIMESTAMPDIFF(SECOND, time_depart, time_return))), '%H:%s:%s') as sum_time
FROM
<your table>
GROUP BY MONTH(time_return)

You can modify the group by to your needs.

There is one issue with the TIME_FORMAT, it will only allow a formatting up to '838:59:59', which is the max value the TIME Datatype can have.

The number of seconds should be accurate...

One last thing: you'll probably have "edge" values, where depart and return are not from the same month... they're counted according to the "group by" to either the month of depart or the month of return.

second edit: you modified the post and added a third requirement.. add it to GROUP BY like

GROUP BY MONTH(..), type

HtH

Answer 3

A typical solution is to use conditional aggregation. Group data by month, then compute separatly the sum of each event type.

It is unclear in which format you expect the durations to be expressed. The following query will return durations in seconds (you can then format them in your application layer or using MySQL date and time functions).

Query:

SELECT 
    DATE_FORMAT(time_depart, '%Y-%M') eventMonth,
    SUM(
        CASE WHEN event_type = 1 
        THEN TIMESTAMPDIFF(SECOND, time_depart, time_return)
        ELSE 0 
    END) trainingTime,
    SUM(
        CASE WHEN event_type = 2 
        THEN TIMESTAMPDIFF(SECOND, time_depart, time_return)
        ELSE 0 
    END) missionTime
FROM events
GROUP BY DATE_FORMAT(time_depart, '%Y-%M')
ORDER BY eventMonth

NB: it is probably a good idea to use the year as a grouping condition too, in case your data spreads over more than 12 months.

Demo on DB Fiddle with your sample data:

| eventMonth    | trainingTime | missionTime |
| ------------- | ------------ | ----------- |
| 2019-February | 5400         | 21600       |