0

I am trying to create a graph that will show the last 30 days. I have the query and it is working, but since we have just started we only have the dates from today onways, What I would like to do is have 30 days in the past also return with the date and total will be 0.

Is there anyway to do this with MYSQL? My Current Query.

SELECT COUNT(DISTINCT(sessionkey)) as total, stamp as day,
    ROUND(UNIX_TIMESTAMP(stamp)/(60 * 60)) AS timekey 
FROM analytics 
WHERE stamp > date_sub(now(), interval 30 day) 
GROUP BY timekey
hildende
  • 832
  • 1
  • 13
  • 19
RussellHarrower
  • 6,470
  • 21
  • 102
  • 204

2 Answers2

0

You can do this in MySQL, however it is not advisable.

The solution is:

Populate a temporary table:

CREATE TEMPORARY TABLE last30days(
myday DATE);

Insert the last 30 days in there:

CREATE PROCEDURE last30DaysProc()
BEGIN
DECLARE v_i INT;
SET v_i=30;
WHILE(v_i>0) LOOP
INSERT INTO last30days(myday) VALUES (date_sub(NOW(), interval v_i days)); 
SET v_i=v_i-1;
END WHILE;
END;

CALL last30DaysProc();

Then join this temp table with your table, and execute the count:

SELECT 
COUNT(DISTINCT(sessionkey)) as total, 
a.myday as myday,
ROUND(UNIX_TIMESTAMP(stamp)/(60 * 60)) AS timekey 
FROM last30days a
LEFT JOIN analytics b ON a.myday=b.stamp
WHERE myday > date_sub(now(), interval 30 day) 
GROUP BY timekey;

I would do this in the code: It is probably shorter to generate the missing dates with count=0 at the moment you are plotting your data.

Norbert
  • 6,026
  • 3
  • 17
  • 40
0

I would do this on php site , it will be more efficient. If count is less than 30 , you can add fake data by a simple foreach loop.

bignick
  • 121
  • 3