Farsheed Feeruzy Farsheed Feeruzy - 5 months ago 20
SQL Question

find total work hours by device

I have a log table:

processlogmodel


id log_time log_type device_id
1061 1/1/2016 9:08:45 PM False 2
1062 1/1/2016 11:19:45 PM False 2
1063 1/2/2016 8:44:46 AM False 2
1064 1/2/2016 3:56:46 AM True 3
1065 1/2/2016 12:51:46 AM True 2
1066 1/1/2016 6:33:46 PM False 2
1067 1/2/2016 8:20:46 AM False 3
1068 1/2/2016 7:57:46 AM True 1
1069 1/1/2016 6:21:46 PM False 1
1070 1/2/2016 8:34:47 AM False 2
1071 1/2/2016 12:18:47 AM True 1
1072 1/1/2016 6:14:47 PM True 3
1073 1/2/2016 8:21:47 AM True 2
1074 1/2/2016 12:39:47 AM False 3
1075 1/1/2016 11:23:47 PM False 1
1076 1/1/2016 8:02:11 PM True 1
1078 1/2/2016 4:02:12 AM False 1
1079 1/1/2016 8:02:11 PM True 2
1080 1/1/2016 10:02:11 PM True 2
1081 1/1/2016 4:54:11 AM False 2
1083 1/1/2016 5:47:11 AM True 2
1084 1/2/2016 4:47:12 AM False 2
1085 1/2/2016 8:39:12 AM True 2


How can I find total work hours by device?

0 means on and 1 means off, I use sqlite3 and Python.

its just log model and i add record with log_type=0 for start working and log_type=1 for end work time for a device.
i want to :
1. find continuous record by devices for calculate work duration
2. find total work hours by device.

Answer

this is my solution:

SELECT device_id, sum(sub) as total 
FROM (
    SELECT t1.device_id, strftime('%s', t2.log_time)-strftime('%s', t1.log_time) as sub 
    FROM app_processlogmodel AS t1 INNER JOIN app_processlogmodel AS t2
    WHERE t1.device_id = t2.device_id AND
    t1.log_type = 0 AND
    t2.log_type = 1 AND
    t2.log_time > t1.log_time
    GROUP BY t1.id HAVING min(strftime('%s', t2.log_time) - strftime('%s', t1.log_time))
    ORDER BY t1.device_id, t1.log_time
)
GROUP BY device_id

and it works :)

Comments