Sum datetime difference for values of same column and group by day

Question

I have a table with 'ON' and 'OFF' values in column activity and another column datetime.

id(AUTOINCREMENT) id_device     activity    datetime
 1                    a            ON        2017-05-26 22:00:00
 2                    b            ON        2017-05-26 05:00:00
 3                    a            OFF       2017-05-27 04:00:00
 4                    b            OFF       2017-05-26 08:00:00
 5                    a            ON        2017-05-28 12:00:00
 6                    a            OFF       2017-05-28 15:00:00

I need to get total ON time by day

day           id_device     total_minutes_on
2017-05-26      a               120
2017-05-26      b               180
2017-05-27      a               240
2017-05-27      b               0
2017-05-28      a               180
2017-05-28      b               0

i have searched and tried answers for another posts, i tried TimeDifference and i get correct total time. I don't find the way to get total time grouped by date

i appreciate your help

Answer 1

I'm not posting this as a definite answer rather it's an experiment for me and hopefully you'll find is useful in your case. Also I would like to mention that the MySQL database version I'm working with is quite old so the method I'm using is also very manual to say the least.

First of all lets extract your expected output:

1. The date value in day need to be repeated twice fro each of id_device a and b.
2. Minutes are calculated based on the activity; if activity is 'ON' until tomorrow, it needs to be calculated until the day end at 24:00:00 while the next day will calculate minutes until the activity is OFF.

What I come up with is this:

• Creating condition (1):

SELECT * FROM
(SELECT DATE(datetime) dtt FROM mytable GROUP BY DATE(datetime)) a,
(SELECT id_device FROM mytable GROUP BY id_device) b
ORDER BY dtt,id_device;

The query above will return the following result:

+------------+-----------+
|    dtt     | id_device |
+------------+-----------+
| 2017-05-26 | a         |
| 2017-05-26 | b         |
| 2017-05-27 | a         |
| 2017-05-27 | b         |
| 2017-05-28 | a         |
| 2017-05-28 | b         |
+------------+-----------+

*Above will only work with all the dates you have in the table. If you want all date regardless if there's activity or not, I suggest you create a calendar table (refer: Generating a series of dates).

So this become the base query. Then I've added an outer query to left join the query above with the original data table:

SELECT v.*,
      GROUP_CONCAT(w.activity ORDER BY w.datetime SEPARATOR ' ') activity, 
      GROUP_CONCAT(TIME_TO_SEC(TIME(w.datetime)) ORDER BY w.datetime SEPARATOR ' ') tr 
FROM
-- this was the first query
(SELECT * FROM
(SELECT DATE(datetime) dtt FROM mytable GROUP BY DATE(datetime)) a,
(SELECT id_device FROM mytable GROUP BY id_device) b
ORDER BY a.dtt,b.id_device) v 
-- 
LEFT JOIN
mytable w
ON v.dtt=DATE(w.datetime) AND v.id_device=w.id_device
GROUP BY DATE(v.dtt),v.id_device

What's new in the query is the addition of GROUP_CONCAT operation on both activity and time value extracted from datetime column which is converted into seconds value. You notice that in both of the GROUP_CONCAT there's a similar ORDER BY condition which is important in order to get the exact corresponding value. The query above will return the following result:

+------------+-----------+----------+-------------+
|    dtt     | id_device | activity |     tr      |
+------------+-----------+----------+-------------+
| 2017-05-26 | a         | ON       | 79200       |
| 2017-05-26 | b         | ON OFF   | 18000 28800 |
| 2017-05-27 | a         | OFF      | 14400       |
| 2017-05-27 | b         | (NULL)   | (NULL)      |
| 2017-05-28 | a         | ON OFF   | 43200 54000 |
| 2017-05-28 | b         | (NULL)   | (NULL)      |
+------------+-----------+----------+-------------+

From here, I've added another query outside to calculate how many minutes and attempt to get the expected result:

SELECT dtt,id_device,
      CASE
      WHEN SUBSTRING_INDEX(activity,' ',1)='ON' AND SUBSTRING_INDEX(activity,' ',-1)='OFF' 
      THEN (SUBSTRING_INDEX(tr,' ',-1)-SUBSTRING_INDEX(tr,' ',1))/60 

      WHEN activity='ON' THEN 1440-(tr/60)

      WHEN activity='OFF' THEN tr/60

      WHEN activity IS NULL AND tr IS NULL THEN 0

      END AS 'total_minutes_on' 
FROM
-- from the last query
(SELECT v.*,
      GROUP_CONCAT(w.activity ORDER BY w.datetime SEPARATOR ' ') activity, 
      GROUP_CONCAT(TIME_TO_SEC(TIME(w.datetime)) ORDER BY w.datetime SEPARATOR ' ') tr 
FROM
-- this was the first query
(SELECT * FROM
(SELECT DATE(datetime) dtt FROM mytable GROUP BY DATE(datetime)) a,
(SELECT id_device FROM mytable GROUP BY id_device) b
ORDER BY a.dtt,b.id_device) v 
-- 
LEFT JOIN
mytable w
ON v.dtt=DATE(w.datetime) AND v.id_device=w.id_device
GROUP BY DATE(v.dtt),v.id_device
--
) z

The last part I do is if the activity value have both ON and OFF on the same day then (OFF-ON)/60secs=total minutes. If activity value is only ON then minutes value for '24:00:00' > 24 hr*60 min= 1440-(ON/60secs)= total minutes, and if activity only OFF, I just convert seconds to minutes because the day starts at 00:00:00 anyhow.

+------------+-----------+------------------+
|    dtt     | id_device | total_minutes_on |
+------------+-----------+------------------+
| 2017-05-26 | a         |              120 |
| 2017-05-26 | b         |              180 |
| 2017-05-27 | a         |              240 |
| 2017-05-27 | b         |                0 |
| 2017-05-28 | a         |              180 |
| 2017-05-28 | b         |                0 |
+------------+-----------+------------------+

Hopefully this will give you some ideas. ;)