How can I grouping an unix time per day?

Question

I have a table like this:

// requests
+----+----------+-------------+
| id | id_user  |  unix_time  |
+----+----------+-------------+
| 1  | 2353     | 1339412843  |
| 2  | 2353     | 1339412864  |
| 3  | 5462     | 1339412894  |
| 4  | 3422     | 1339412899  |
| 5  | 3422     | 1339412906  |
| 6  | 2353     | 1339412906  |
| 7  | 7785     | 1339412951  |
| 8  | 2353     | 1339413640  |
| 9  | 5462     | 1339413621  |
| 10 | 5462     | 1339414490  |
| 11 | 2353     | 1339414923  |
| 12 | 2353     | 1339419901  |
| 13 | 8007     | 1339424860  |
| 14 | 7785     | 1339424822  |
| 15 | 2353     | 1339424902  |
+----+----------+-------------+

I want to grouping unix_time column based on separated days. Actually I'm trying to make this for an specific user:

As you see I need tow numbers for an user:

• the number of all days which there is a foot print of the user into requests table
• the number of biggest consecutive days

How can I do that?

Actually I can use WHERE id_user = :id to select user's rows. And I can calculate the number of days by SUM(). And by using MAX() I can calculate the biggest consecutive range. Just I need to grouping those unix times.

Answer 1

Please give it a try:

SELECT 
t.id_user,
COUNT(*) totalVisits,
MAX(t.max_cons) maxCons
FROM 
(SELECT
        id_user,
        @lastUnixTime AS lastUnixTimeOfuser,
        IF(@uid <> id_user, @currentMax := 1 , @currentMax),
        IF(@uid <> id_user, @lastUnixTime := 0, @lastUnixTime := @lastUnixTimeOfLastRecord),

        IF(@uid = id_user, 
                    IF((@lastUnixTime + 86400) >= utime, @currentMax := @currentMax + 1, @currentMax := 1), @lastUnixTime := 0),
        IF(@currentMax > @max, @max := @currentMax, @max ),
        IF(@uid <> id_user , @max := 1 ,@max),
        @uid := id_user,    
        @lastUnixTimeOfLastRecord := utime,
        @max AS max_cons
    FROM
        (
        SELECT 
            id_user,
            (unix_time DIV 86400) * 86400 AS utime
        FROM requests 
        GROUP BY id_user, utime ) dayWiseRequestTable ,
        (
            SELECT
                @uid := 0,
                @currentMax := 0,
                @max := 0,
                @lastUnixTime := 0,
                @lastUnixTimeOfLastRecord := 0
        ) vars

    ORDER BY id_user, utime) t
GROUP BY t.id_user;

SQL FIDDLE DEMO

Output:

The final output looks like below:

id_user Total_Visits    Maximum_Consecutive_Visits
2353        7                    2
3422        2                    2
5462        3                    2
7785        2                    1
8007        1                    1

EDIT:

In order to get output for a specific user you need to add a WHERE clause in the inner query.

Please check this

SQL FIDDLE

Answer 2

Use can extract the day using from_unixtime(). Then you can get count the days using variables:

select id_user, d,
       (@rn := if(@di = concat_ws(':', d - interval 1 day, id_user), @rn + 1,
                  if(@di :=  concat_ws(':', d, id_user), 1, 1)
                 )
       ) as rn
from (select id_user, date(from_unixtime(unix_time)) as d
      from t
      group by id_user, d
     ) cross join
     (select @di := '', @rn := 0) params
order by id_user, d;

From here to the summary is just an aggregation:

select id_user, count(*) as numdays, max(rn) as maxconsecutive
from (select id_user, d,
             (@rn := if(@di = concat_ws(':', d - interval 1 day, id_user), @rn + 1,
                        if(@di :=  concat_ws(':', d, id_user), 1, 1)
                       )
             ) as rn
      from (select id_user, date(from_unixtime(unix_time)) as d
            from t
            group by id_user, d
           ) cross join
           (select @di := '', @rn := 0) params
      order by id_user, d
     ) ud
group by id_user;

Here is a SQL Fiddle illustrating the code.