MySQL to fill in missing dates when using GROUP BY DATE(table.timestamp) without joining on temporary table

Question

after reading through a couple similar Qs/As I haven't quite found the solution I'm looking for. The table data I have is GROUP BY DATE(timestamp) and returning a count, example result:

[timestamp] => 2010-05-12 20:18:36
[count] => 10


[timestamp] => 2010-05-14 10:10:10
[count] => 8

Without using a temporary table, or calendar table is there a way to fill in those missing dates? so that with the same table data would return:

[timestamp] => 2010-05-12 20:18:36
[count] => 10


/**
 * I would like to have this row added:
 */
[timestamp] => 2010-05-13 00:00:00
[count] => 0

[timestamp] => 2010-05-14 10:10:10
[count] => 8

Thanks!

possible duplicate of MySQL: Select All Dates In a Range Even If No Records Present — Mark Byers
– Mark Byers, Commented May 16, 2010 at 16:28

Mark Byers · Accepted Answer · 2010-05-16 16:27:23Z

0

The best way to solve this is to find the missing results on the client side and add them when you present the results.

answered May 16, 2010 at 16:27

Mark Byers

844k202 gold badges1.6k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

skilleo · Accepted Answer · 2010-05-24 04:52:43Z

0

I didn't find the exact solution i was looking for, but I think found a method that works enough for my needs. I created a traffic table where the first visitor of the day will trigger a row insert for the day and now I can join on this table to get a daily record of whatever. The only problem however, is if there is no traffic for a day that date won't exist in the table. This can be solved by running a daily cron to insert a new row.

answered May 24, 2010 at 4:52

skilleo

2,4811 gold badge27 silver badges35 bronze badges

Comments

superiggy · Accepted Answer · 2011-03-23 20:00:22Z

0

You should create a tally table (only 1 column: date), and fill it beforehand. You can run a cron script to fill 1 year, and run it once a year.

answered Mar 23, 2011 at 20:00

superiggy

1,0618 silver badges15 bronze badges

Collectives™ on Stack Overflow

MySQL to fill in missing dates when using GROUP BY DATE(table.timestamp) without joining on temporary table

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related