Filling Gaps in Dates Returned from Database - pure SQL solution possible?

Question

I have this query:

SELECT COUNT(*) as clicks, DATE_FORMAT(FROM_UNIXTIME(click_date), '%w %M %Y') as point 
FROM tracking 
WHERE click_date < $end_date AND click_date > $start_date 
GROUP BY DAY(FROM_UNIXTIME(click_date))

Where $start_date is two weeks ago and $end_date is today's date.

I am trying find all clicks made each day for a particular date range. I also want to include days where there has been no clicks. Since naturally there isn't an entry for these in my database I need to include them some how, how can I best do this whilst showing all dates from start date to end date. This what I currently have, lots of gaps for this two week date range.

Array
(
    [0] => Array
        (
            [clicks] => 17
            [point] => 0 February 2011
        )

    [1] => Array
        (
            [clicks] => 3
            [point] => 1 February 2011
        )

    [2] => Array
        (
            [clicks] => 14
            [point] => 5 February 2011
        )

    [3] => Array
        (
            [clicks] => 1
            [point] => 1 February 2011
        )

    [4] => Array
        (
            [clicks] => 8
            [point] => 2 February 2011
        )

)

Can this possibly be done via a pure SQL query or do I have to use some php logic?

Btw, why do I have 0 February 2011 as my first date! Hmm, I also seem to have duplicate dates, that shouldn't happen, maybe my GROUP BY isn't working correctly?

Thanks all for any help.

RichardTheKiwi · Accepted Answer · 2011-02-21T00:46:34.863

Can this possibly be done via a pure SQL query or do I have to use some php logic?

Yes, it is better to create a Numbers table (single column N) that contains nothing but the numbers 0 to 999. It can be used for many things, not least a query like the below:

SELECT COUNT(t.click_date) as clicks,
    DATE_FORMAT(adddate($start_date, interval N day), '%d %M %Y') as point 
FROM Numbers
LEFT JOIN tracking t
    ON t.click_date >= adddate($start_date, interval N day)
    and t.click_date < adddate($start_date, interval (N+1) day)
WHERE N between 0 and datediff($start_date, $end_date)
GROUP BY N

Btw, why do I have 0 February 2011 as my first date

You're using the wrong format. It's UPPER case W not lower for day-of-week, so '%W %M %Y' or '%d %M %Y' for day-of-month. http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format

maybe my GROUP BY isn't working correctly?

You are using GROUP BY DAY(FROM_UNIXTIME(click_date)) note "day" not weekday, but you are displaying (or trying to) "%W" (weekday) - pick one, don't mix them.

EDIT: If you prefer not to materialize (create as a real table) a Numbers sequence table, you can construct one on the fly. It won't be pretty.

Note: N1, N2 and N3 below combine to give a possible range of 0-999

SELECT COUNT(t.click_date) as clicks,
    DATE_FORMAT(adddate($start_date, interval N day), '%d %M %Y') as point 
FROM (
    select N1 * 100 + N2 * 10 + N3 as N
    from (
    select 0 N1 union all select 1 union all select 2 union all
    select 3 union all select 4 union all select 5 union all
    select 6 union all select 7 union all
    select 8 union all select 9) N1
    cross join (
    select 0 N2 union all select 1 union all select 2 union all
    select 3 union all select 4 union all select 5 union all
    select 6 union all select 7 union all
    select 8 union all select 9) N2
    cross join (
    select 0 N3 union all select 1 union all select 2 union all
    select 3 union all select 4 union all select 5 union all
    select 6 union all select 7 union all
    select 8 union all select 9) N3
    ) Numbers
LEFT JOIN tracking t
    ON t.click_date >= adddate($start_date, interval N day)
    and t.click_date < adddate($start_date, interval (N+1) day)
WHERE N between 0 and datediff($start_date, $end_date)
GROUP BY N

EDIT #2: A straight Dates table

Put this in a new window in phpMyAdmin or run it as a batch. It creates a table named Dates, with every single date from day 1900-01-01 (or change in the script) to 2300-01-01 (or change).

DROP PROCEDURE IF EXISTS FillDateTable;

delimiter //
CREATE PROCEDURE FillDateTable()
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
  drop table if exists datetable;
  create table datetable (thedate datetime primary key, isweekday smallint);

  SET @x := date('1900-01-01');
  REPEAT 
    insert into datetable (thedate, isweekday) SELECT @x, case when dayofweek(@x) in (1,7) then 0 else 1 end;
    SET @x := date_add(@x, interval 1 day);
    UNTIL @x > date('2300-01-01') END REPEAT;
END//
delimiter ;

CALL FillDateTable;

With such a utility table, your query can be just

SELECT COUNT(t.click_date) as clicks,
    DATE_FORMAT(thedate, '%d %M %Y') as point 
FROM Dates
LEFT JOIN tracking t
    ON t.click_date >= thedate
    and t.click_date < adddate(thedate, interval 1 day)
WHERE thedate between $start_date and $end_date
GROUP BY thedate

If you're going to go to the (slight) trouble of making a new table, in this case it's probably better to create a calendar table. Then you could just do an outer join on it instead of all the calls to the date functions. It would probably run faster, too. — Mike Sherrill 'Cat Recall', Feb 20 '11 at 23:58
@Catcall - how true. I use a generic number table for a lot of things, although I concede having a number *as well as* a date table would be even better. — RichardTheKiwi, Feb 21 '11 at 00:03
I am loving your answer! I am also interested in this date table,how would that work?? Surely, not a table of dates?! How would I utilize it? — Abs, Feb 21 '11 at 00:25
Awesome! Now I understand how it works, it works great for me. Thank you very much Richard! — Abs, Feb 21 '11 at 01:15
@Abs: I wrote an answer specific to PostgreSQL, but the syntax is easy to understand, and the idea is portable, although the statements are not. See http://stackoverflow.com/questions/5030546/how-to-get-the-count-of-current-month-sundays-in-psql — Mike Sherrill 'Cat Recall', Feb 21 '11 at 17:13

score 0 · Answer 2 · answered Feb 20 '11 at 19:25

In my opinion you are better off doing this type of logic in your code. But if you wanted to do it in pure SQL you could construct a query to give you the results of all the days between one day and the next ... either by inserting into a temp table or an in memory table... then left join that into your results so that you get all the days regardless of if there were results for that day.

score 0 · Answer 3 · answered Feb 20 '11 at 19:25

0

I'd stick to PHP logic, looping between the lowest date and the highest date, and incrementing one day at a time.

You could probably do it in SQL with some fancy joins, but I won't even start to consider that nastiness!

BTW, %w is the day of the week, starting 0=Sunday. You probably wanted %d.

answered Feb 20 '11 at 19:25

Lightness Races in Orbit

378,754
76
643
1,055

Ah I see, I'll use the `%d`. Yeh, I thought SQL was going to get complicated! – Abs Feb 20 '11 at 19:29

Filling Gaps in Dates Returned from Database - pure SQL solution possible?

3 Answers3

Linked