如何计算两个时间戳之间的差异，然后逐个月地在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

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       |