mysql: select a date range, with gaps filled

[httpwebwitch]

hope this isn't too hard

I have data that looks like this:

2008-01-03 ¦ 5
2008-01-12 ¦ 2
2008-01-13 ¦ 3
2008-01-24 ¦ 2

I want to select a date range, like January, and get results with all the gaps filled with a default value, like 0 or 'NULL'. for instance:

2008-01-01 ¦ 0
2008-01-02 ¦ 0
2008-01-03 ¦ 5
2008-01-04 ¦ 0
2008-01-05 ¦ 0
2008-01-06 ¦ 0
2008-01-07 ¦ 0
2008-01-08 ¦ 0
2008-01-09 ¦ 0
2008-01-10 ¦ 0
2008-01-11 ¦ 0
2008-01-12 ¦ 2
2008-01-13 ¦ 3
2008-01-14 ¦ 0
2008-01-15 ¦ 0
2008-01-16 ¦ 0
2008-01-17 ¦ 0
2008-01-18 ¦ 0
2008-01-19 ¦ 0
2008-01-20 ¦ 0
2008-01-21 ¦ 0
2008-01-22 ¦ 0
2008-01-23 ¦ 0
2008-01-24 ¦ 2
2008-01-25 ¦ 0
2008-01-26 ¦ 0
2008-01-27 ¦ 0
2008-01-28 ¦ 0
2008-01-29 ¦ 0
2008-01-30 ¦ 0

Is there a simple SELECT that will do this?

[httpwebwitch]

Parameters for this query are startdate and enddate.

I'm going to loop through the results to produce a graph. So it's just as important for me to get those in-between dates as it is to get the data associated with some of them...

For instance I might pass in ('2008-02-20','2008-03-04') and I'd like the data returned to know whether there is a leap day in the range.

[httpwebwitch]

OK it's insane that this took 3 hours, but here's a half-solution. A stupid one, IMHO. I'm still looking for a better way to do this.

First, I had to create a temporary table full of integers.

CREATE TEMPORARY TABLE Integers (
 i INT UNSIGNED NOT NULL DEFAULT 0
);
INSERT INTO Integers (i) VALUES (1); INSERT INTO Integers (i) VALUES (2); INSERT INTO Integers (i) VALUES (3); INSERT INTO Integers (i) VALUES (4);
INSERT INTO Integers (i) VALUES (5); INSERT INTO Integers (i) VALUES (6); INSERT INTO Integers (i) VALUES (7); INSERT INTO Integers (i) VALUES (8);
INSERT INTO Integers (i) VALUES (9); INSERT INTO Integers (i) VALUES (10);
INSERT INTO Integers (i) VALUES (11); INSERT INTO Integers (i) VALUES (12); INSERT INTO Integers (i) VALUES (13); INSERT INTO Integers (i) VALUES (14);
INSERT INTO Integers (i) VALUES (15); INSERT INTO Integers (i) VALUES (16); INSERT INTO Integers (i) VALUES (17); INSERT INTO Integers (i) VALUES (18);
INSERT INTO Integers (i) VALUES (19); INSERT INTO Integers (i) VALUES (20);
INSERT INTO Integers (i) VALUES (21); INSERT INTO Integers (i) VALUES (22); INSERT INTO Integers (i) VALUES (23); INSERT INTO Integers (i) VALUES (24);
INSERT INTO Integers (i) VALUES (25); INSERT INTO Integers (i) VALUES (26); INSERT INTO Integers (i) VALUES (27); INSERT INTO Integers (i) VALUES (28);
INSERT INTO Integers (i) VALUES (29); INSERT INTO Integers (i) VALUES (30);

Then I could use that to iterate through them using day numbers

SELECT i as x,
(SELECT FROM_DAYS(TO_DAYS(NOW()) - x)) as date,
(SELECT value FROM statistics WHERE 
date = (FROM_DAYS(TO_DAYS(NOW()) - x))
) as value
FROM Integers WHERE i > 0 AND i < 24

This query grabs 24 days previous to NOW(). It could be adapted to grab dates between A and B.

And don't forget to

DROP TABLE Integers

Is it just me, or is it crazy to need a table full of positive integers? What if the DATEDIFF() exceeds the COUNT() of my Integers table?

There's got to be a better way than this

[rocknbil]

Well, I would do this differently - maybe not the perfect way - but on two points.

One, I don't see any reason to clog up a table with values just meant as "placeholders." Two, it's more likely to run slower with actual table selects. I'd probably do this mostly in programming. Depending on what's easier, I'd store days in month somewhere or get it from a select. Once you have the month length,

$month=01;
$year=2008;

for ($i (1..$days_in_month) { 
 $result = ''; 
 $da = (length($i)>1)?$i:'0'.$i; 
 $dt = "$year-$month-$da"; 
  select data from table where day = '$dt'; 
 $output = ($result)?$result:0; 
} 

Where $result is the query result. Note I set it to an empty string at the beginning so it doesn't stay populated with the previous value if nothing is found in the database.

[httpwebwitch]

ah. So you're suggesting that I bring the logic up one level to the application layer (PHP) not the data access layer (SQL), and make my loop happen there, where I've got powerful built-in functions for working with dates. So if I am requesting a range of 20 days, I make 20 small SQL queries, one for each day.

Though I despise loops that make SQL queries (I always imagine my anthropomorphized database being sprayed with a machine gun), that's probably more efficient than the 1 SQL query I have in the example above, which involves one query (to get the integers) with two subqueries (to get the data for each). And it doesn't require a stupid Temp Table full of numbers.

So yeah, this is better - if my solution was a house of straw, your idea is made of sticks. Is there a brick house somewhere? When this query goes live, the amount of traffic it'll get will huff and puff like a Big Bad Wolf... </metaphor>

[LifeinAsia]

My approach would be a little different to avoid multiple query calls. I'm not a PHPer (PHPite? PHPist?), but I assume that PHP has arrays.

Step 1: (in SQL) Do 1 query from the DB to get the data that is there.
Step 2: (in PHP) Initialize an array and populate it with zero values for each data.
Step 3: (in PHP) Loop through the query and assign the data to the relevant array position.
Step 4: (in PHP) Loop through the array and output the values.

[httpwebwitch]

L.I.A., though I'm reluctant to "let go" of the dream of an all-SQL solution, I think that's the best solution so far. PHP has associative arrays that will work very nicely with date strings as keys.

[leadegroot]

I'm with LifeinAsia in method - I think the overheads of the extra SQL would outweigh the neatness.
Get what you can out of the SQL, then fill in the gaps in PHP
:)