CODEWITHSUNDEEP
mysql
Xaver
Xaver

Reputation: 11662

get totals each day based on a given timestamp

I have a simple table:

user   | timestamp
===================
Foo    | 1440358805
Bar    | 1440558805
BarFoo | 1440559805
FooBar | 1440758805

I would like to get a view with total number of users each day:

date       | total
===================
...
2015-08-23 | 1    //Foo
2015-08-24 | 1    
2015-08-25 | 1    
2015-08-26 | 3    //+Bar +BarFoo
2015-08-27 | 3    
2015-08-28 | 4    //+FooBar
...

What I currently have is

SELECT From_unixtime(a.timestamp, '%Y-%m-%d')        AS date, 
       Count(From_unixtime(a.timestamp, '%Y-%m-%d')) AS total
FROM   thetable AS a
GROUP  BY From_unixtime(a.timestamp, '%Y-%m-%d')
ORDER  BY a.timestamp ASC

which counts only the user of a certain day:

date       | total
===================
2015-08-23 | 1    //Foo
2015-08-26 | 2    //Bar +BarFoo
2015-08-28 | 1    //FooBar

I've prepared a sqlfiddle

EDIT

The solution by @splash58 returns this result:

date       | @t:=coalesce(total, @t)
==================================
2015-08-23 | 1
2015-08-26 | 3
2015-08-28 | 4
2015-08-21 | 4
2015-08-22 | 4
2015-08-24 | 4
2015-08-25 | 4
2015-08-27 | 4
2015-08-29 | 4
2015-08-30 | 4

Upvotes: 4

Views: 131

Answers (4)

splash58
splash58

Reputation: 26153

To save order of dates, i think, we need to wrap query in one more select

select date, @n:=@n + ifnull(total,0) total  
   from 
     (select Calendar.date, total   
          from Calendar 
            left join 
               (select From_unixtime(timestamp, '%Y-%m-%d') date, count(*) total 
                    from thetable 
                  group by date) t2 
            on Calendar.date= t2.date
       order by date) t3
     cross join (select @n:=0) n

Demo on sqlfiddle

Upvotes: 1

splash58
splash58

Reputation: 26153

drop table if exists thetable;
create table thetable (user text, timestamp int);
insert into thetable values
('Foo', 1440358805),
('Bar', 1440558805),
('BarFoo', 1440559805),
('FooBar', 1440758805);

DROP PROCEDURE IF EXISTS insertTEMP;
DELIMITER // 
CREATE PROCEDURE insertTEMP (first date, last date) begin 
drop table if exists Calendar;
CREATE TEMPORARY TABLE Calendar (date date);
WHILE first <= last DO
INSERT INTO Calendar  Values (first);
SET first = first + interval 1 day; 
END WHILE;
END // 
DELIMITER ;

call insertTEMP('2015-08-23', '2015-08-28');

select Calendar.date, @t:=coalesce(total, @t) 
    from Calendar 
         left join 
         (select date, max(total) total 
             from (select From_unixtime(a.timestamp, '%Y-%m-%d') AS date,
                          @n:=@n+1 AS total
                       from thetable AS a, (select @n:=0) n
                    order by a.timestamp ASC) t1
                    group by date ) t2 
         on Calendar.date= t2.date, 
         (select @t:=0) t

result

date,       @t:=coalesce(total, @t)
2015-08-23  1
2015-08-24  1
2015-08-25  1
2015-08-26  3
2015-08-27  3
2015-08-28  4

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269693

You can get the cumulative values by using variables:

SELECT date, total, (@cume := @cume + total) as cume_total
FROM (SELECT From_unixtime(a.timestamp, '%Y-%m-%d') as date, Count(*) AS total
      FROM thetable AS a
      GROUP BY From_unixtime(a.timestamp, '%Y-%m-%d')
     ) a CROSS JOIN
     (SELECT @cume := 0) params
 ORDER BY date;

This gives you the dates that are in your data. If you want additional dates (where no users start), then one way is a calendar table:

SELECT c.date, a.total, (@cume := @cume + coalesce(a.total, 0)) as cume_total
FROM Calendar c JOIN
     (SELECT From_unixtime(a.timestamp, '%Y-%m-%d') as date, Count(*) AS total
      FROM thetable AS a
      GROUP BY From_unixtime(a.timestamp, '%Y-%m-%d')
     ) a
     ON a.date = c.date CROSS JOIN
     (SELECT @cume := 0) params
 WHERE c.date BETWEEN '2015-08-23' AND '2015-08-28'
 ORDER BY c.date;

You can also put the dates explicitly in the query (using a subquery), if you don't have a calendar table.

Upvotes: 2

asd-tm
asd-tm

Reputation: 5263

You can use function 

TIMESTAMPDIFF(DAY,`timestamp_field`, CURDATE())

You will not have to convert timestamp to other field dypes.

Upvotes: 1

Related Questions