Group by time intervals

Asked May 12 '14 at 16:08

Active May 12 '14 at 16:17

Viewed 145 times

I'm trying to group some data by time intervals (in this case, day intervals).

So far i have this code:

SELECT DATE(timestamp) AS date,
       COUNT(*) AS transactions
  FROM trace
 GROUP BY DATE(timestamp)
 ORDER BY DATE(timestamp)

With this query, what i get is something like:

date,transactions
2014-01-20,18
2014-01-21,18
2014-01-24,4
2014-01-25,3

As you can see, there is a gap between the 21th day and the 24th, but I want to take into acount those days too:

date,transactions 
2014-01-20,18 
2014-01-21,18 
2014-01-22,0
2014-01-23,0
2014-01-24,4 
2014-01-25,3

Anyone have any idea how to solve this? Thanks!!!!

edit1: I'm using Mysql.

edited May 12 '14 at 16:17

BWS

asked May 12 '14 at 16:08

qnoxo

What RDBMS product are you using ? – Charles Bretana May 12 '14 at 16:13
There is a recommendation at http://stackoverflow.com/questions/14105018/generating-a-series-of-dates to use a calendar table in this case. – mc110 May 12 '14 at 16:21

0 Answers0