Find Overlaps Between Timestamps In Different Rows In A Mysql Db, Group By Days And ID's

I have a table, where events of certain objects are listed. There are two events: 'movement' and 'load'. They can start and end, and these events are listed with timestamps when th

Solution 1:

As shown by Gordon, grouping is made possible by moving the filtering into the outermost SELECT. The search for the loading events that need to enclose the movement events is done in the same way as in the earlier answer:

SELECT id,
       date(timestamp, 'unixepoch') AS date,
       (SUM(CASE WHEN event = 'movement end'   THEN timestamp END) -
        SUM(CASE WHEN event = 'movement start' THEN timestamp END)
       ) AS all_movement,
       (SUM(CASE WHEN event = 'load end'   THEN timestamp END) -
        SUM(CASE WHEN event = 'load start' THEN timestamp END)
       ) AS all_load,
       (SUM(CASE WHEN event = 'movement end' AND
                      (SELECT event
                       FROM Table1 b
                       WHERE timestamp = (SELECT MIN(timestamp)
                                          FROM Table1 c
                                          WHERE c.timestamp >= a.timestamp
                                            AND c.id = a.id
                                            AND c.event LIKE 'load %')
                         AND b.id = a.id
                         AND b.event LIKE 'load %'
                      ) = 'load end'
            THEN timestamp END) -
        SUM(CASE WHEN event = 'movement start' AND
                      (SELECT event
                       FROM Table1 b
                       WHERE timestamp = (SELECT MAX(timestamp)
                                          FROM Table1 c
                                          WHERE c.timestamp <= a.timestamp
                                            AND c.id = a.id
                                            AND c.event LIKE 'load %')
                         AND b.id = a.id
                         AND b.event LIKE 'load %'
                      ) = 'load start'
            THEN timestamp END)
       ) AS load_movement
FROM Table1 a
GROUP BY id,
         date(timestamp, 'unixepoch')

Solution 2:

The problem is the query that you are starting with. I simply don't understand what the third column is. But, the first two are much more easily calculated using:

select (sum(case when event = 'movement end' then timestamp end) -
        sum(case when event = 'movement start' then timestamp end)
       ),
       (sum(case when event = 'load end' then timestamp end) -
        sum(case when event = 'load start' then timestamp end)
       )
from table1

From this, it should be trivial to add the group by:

select id, date(timestamp),
        (sum(case when event = 'movement end' then timestamp end) -
        sum(case when event = 'movement start' then timestamp end)
       ),
       (sum(case when event = 'load end' then timestamp end) -
        sum(case when event = 'load start' then timestamp end)
       )
from table1
group by id, date(timestamp)