Receive totals every day based on a given timestamp

Question

I have a simple table:

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

I would like to get an idea with the total number of users every 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
...

I currently have

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 takes into account 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 prepared sqlfiddle

EDIT

The @ splash58 solution 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

Answer 1

To keep date order, I think we need to wrap the query with another choice

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

Answer 2

You can get cumulative values ​​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;

, . ( ), - :

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;

( ), .

Answer 3

You can use the function

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

You do not need to convert the timestamp to other field types.

Answer 4

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