CODEWITHSUNDEEP
mysqlsql
Hashim Zahid
Hashim Zahid

Reputation: 627

I want to calculate time between 2 Dates of a day with multiple rows in mysql

I'm using this query to calculate the login time of a user on the app for the whole day and previous 5 days

Select 
  sec_to_time(sum(time_to_sec(TIMEDIFF((IFNULL(logoff_time, ADDTIME(now(), '05:00:00'))),login_time)))) as online_time 
from tb_sessions  
WHERE 
  (login_time BETWEEN DATE(DATE_ADD(now(), INTERVAL (-6) DAY)) 
AND
  ADDTIME(now(), '5:00:00')) AND user_id = 30982
AND TIME(`login_time`) between "00:00:00" AND "23:59:59"
group by DATE(login_time)

Now i have some new requirements:

Calculate time from 07:00:00 to 23:59:59

My Table: tb_sessions

id |   user_id   |       login_time      |   logoff_time

1        3098       2017-06-10 06:30:00    2017-06-10 07:45:00
2        3098       2017-06-10 07:45:01    2017-06-10 08:30:00

By using above query total oline time is = 02:00:00 But i want only time from 7:00 to 8:30, so total time will be = 1:30:00 I make some changes in query with cases but no success.

You can check my query on the below link:

http://sqlfiddle.com/#!9/4620af/12

Upvotes: 0

Views: 308

Answers (1)

trincot
trincot

Reputation: 350766

You could use greatest to take the latest of the dates login_time and 7:00 on the same day, and then use greatest again to exclude negative time differences (when also logoff time is before 7:00):

Select   date(login_time) date,
         time_format(sec_to_time(sum(greatest(0, time_to_sec(timediff(
             ifnull(logoff_time, now()), 
             greatest(login_time, date_add(date(login_time), interval 7 hour))
         ))))), '%H:%i:%s') online
from     tb_sessions  
where    login_time between date(date_add(now(), interval (-3) day)) and now() 
and      user_id = 3098
and      time(login_time) between "00:00:00" and "23:59:59"
group by date(login_time)

See it run on sqlfiddle

Explanation

The inner greatest call looks like this:

greatest(login_time, date_add(date(login_time), interval 7 hour))

The second argument takes the date-only from the login_time, so it corresponds to midnight of that day, and then adds 7 hours to it: so this represents 7:00 on that day. greatest will return the latest of these two timestamps. If the first argument represents a time than 7:00, it will be returned. If not, the second argument (i.e. 7:00) will be returned.

The outer greatest call looks like this:

greatest(0, time_to_sec(timediff(....)))

This will make sure the time difference is not negative. Take this example record:

   login_time   |   logoff_time
----------------+----------------
2017-06-01 6:30 | 2017-06-01 6:45

In this case the innermost greatest will return 2017-06-01 7:00, because 6:30 is too early. But that will make timediff() return a negative time interval: -15 minutes. What we really want is 0, because there is no time the user was logged on after 7:00. This is what greatest will do: greatest(0, -15) = 0, so the negative value will be eliminated and will not influence the sum.

Condition on login_time

I left the condition time(login_time) between "00:00:00" and "23:59:59" there, but it really does not do anything, since that is true for all times (unless they are null, but then they would not pass the first condition either).

Edit after New Requirements

In comments you asked how to group by each day when a user doesn't log off on the same day but stays online until 1 or 2 days later.

In that case you need a helper table that will list all days you want to see in the output. This could for instance be seven records for the 7 last days.

Then you have to join your table with it so that there is at least an overlap of the user's session with such a reference date. The calculation of the online time will have to take into account that the log off time might not be before mid night.

Here is the updated query:

select   ref_date date,
         time_format(sec_to_time(sum(greatest(0, time_to_sec(timediff(
             least(ifnull(logoff_time, now()), date_add(ref_date, interval 1 day ), now()), 
             greatest(login_time,              date_add(ref_date, interval 7 hour))
         ))))), '%H:%i:%s') online
from     (  select date(date_add(now(), interval (-6) DAY)) as ref_date union all 
            select date(date_add(now(), interval (-5) DAY)) union all 
            select date(date_add(now(), interval (-4) DAY)) union all 
            select date(date_add(now(), interval (-3) DAY)) union all 
            select date(date_add(now(), interval (-2) DAY)) union all 
            select date(date_add(now(), interval (-1) DAY)) union all 
            select date(now())
         ) ref
inner join tb_sessions 
        on login_time < date_add(ref_date, interval 1 day)
       and logoff_time > date_add(ref_date, interval 7 hour) 
where    user_id = 3098
group by ref_date

See it run on sqlfiddle.

Upvotes: 1

Related Questions