Finding total active hours by calculating difference between TimeDate records

Question

I have a table to register users logs every one minute and other activities using DateTime for each user_id

This is a sample data of my table

id   |   user_id   |     log_datetime
------------------------------------------
1    |      1      |   2016-09-25 13:01:08 
2    |      1      |   2016-09-25 13:04:08 
3    |      1      |   2016-09-25 13:07:08 
4    |      1      |   2016-09-25 13:10:08 
5    |      2      |   2016-09-25 13:11:08 
6    |      1      |   2016-09-25 13:13:08 
7    |      2      |   2016-09-25 13:13:09 
8    |      2      |   2016-09-25 13:14:10 

I would like to calculate the total active time on the system

---

UPDATE: Expected Output

For Example user_id 1 his total available time should be 00:12:00

Since his hours and seconds are same so I'll just subtract last log from previous then previous from next previous and so on then I'll sum all subtracted values this a simple for

Simply I want to loop through the data from last record to first record with in my range

this is a simple formula I hope that make my question clear

SUM((T< n > - T< n-1 >) + (T< n-1 > - T< n-2 >) ... + (T< n-x > - T< n-first >))

Since user_id 1 his hours and seconds are the same then I'll calculate the minutes only.

(13-10)+(10-7)+(7-4)+(4-1) = 12

user_id   |       total_hours
---------------------------------
   1      |        00:12:00 
   2      |        00:03:02

---

I did this code

SET @start_date = '2016-09-25';
SET @start_time = '13:00:00';

SET @end_date = '2016-09-25';
SET @end_time = '13:15:00';

SELECT 
`ul1`.`user_id`, SEC_TO_TIME(SUM(TIME_TO_SEC(`dl1`.`log_datetime`))) AS total_hours 
FROM 
`users_logs` AS `ul1`
JOIN `users_logs` AS `ul2` 
    ON `ul1`.`id` = `ul2`.`id`

WHERE  
`ul1`.`log_datetime` >= CONCAT(@start_date, ' ', @start_time)
AND
`ul2`.`log_datetime` <= CONCAT(@end_date, ' ', @end_time)


GROUP BY `ul1`.`user_id`

But this code Sum all Time not getting the difference. This is the output of the code

user_id   |       total_hours
---------------------------------
   1      |        65:35:40 
   2      |        39:38:25

How can I calculate the Sum of all difference datetime, then I want to display his active hours every 12 hours (00:00:00 - 11:59:59) and (12:00:00 - 23:59:59) with in selected DateTime Period at the beginning of the code

So the output would look like this (just an dummy example not from given data)

user_id   |  total_hours  |   00_12_am  |   12_00_pm  |  
-------------------------------------------------------
   1      |   10:10:40    |   02:05:20  |   08:05:20  |
   2      |   04:10:20    |   01:05:10  |   03:05:30  |

Thank you

Answer 1

So you log every minute and if a user is available there is a log entry.

Then count the logs per user, so you have the number of total minutes.

select user_id, count(*) as total_minutes
from user_logs
group by user_id;

If you want them displayed as time use sec_to_time:

select user_id, sec_to_time(count(*) * 60) as total_hours
from user_logs
group by user_id;

As to conditional aggregation:

select 
  user_id, 
  count(*) as total_minutes,
  count(case when hour(log_datetime) < 12 then 1 end) as total_minutes_am,
  count(case when hour(log_datetime) >= 12 then 1 end) as total_minutes_pm
from user_logs
group by user_id;

UPDATE: In order to count each minute just once count distinct minutes, i.e. DATE_FORMAT(log_datetime, '%Y-%m-%d %H:%i'). This can be done with COUNT(DISTINCT ...) or with a subquery getting distinct values.

The complete query:

select 
  user_id, 
  count(*) as total_minutes,
  count(case when log_hour < 12 then 1 end) as total_minutes_am,
  count(case when log_hour >= 12 then 1 end) as total_minutes_pm
from 
(
  select distinct 
    user_id, 
    date_format(log_datetime, '%y-%m-%d %h:%i') as log_moment,
    hour(log_datetime) as log_hour
  from.user_logs
) log
group by user_id;