Mysql: Select all data between two dates

Question

I have a mysql table with data connected to dates. Each row has data and a date, like this: 2009-06-25 75 2009-07-01 100 2009-07-02 120

I have a mysql query that select all data between two dates. This is the query:

"SELECT data FROM tbl WHERE date BETWEEN date1 AND date2"

My problem is that I also need to get the rows between date1 and date2 even if there is no data for a day.

So my query would miss the dates that are empty between 2009-06-25 and 2009-07-01.

Can I in some way add these dates with just 0 as data?

Answer 1

Do you have a table that has all dates? If not, you might want to consider implementing a calendar table and left joining your data onto the calendar table.

Answer 2

No there is just rows for the dates containing data. Can I in some way add the dates not in the table without using a calendar table?

I could loop through the dataset afterwards and then add the dates that is missing but that seems like a stupid implementation. Is there a way of doing this inside my mysql query?

Answer 3

You can use a concept that is frequently referred to as 'calendar tables'. Here is a good guide on how to create calendar tables in MySql:

-- create some infrastructure
CREATE TABLE ints (i INTEGER);
INSERT INTO ints VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);

-- only works for 100 days, add more ints joins for more
SELECT cal.date, tbl.data
FROM (
    SELECT '2009-06-25' + INTERVAL a.i * 10 + b.i DAY as date
    FROM ints a JOIN ints b
    ORDER BY a.i * 10 + b.i
) cal LEFT JOIN tbl ON cal.date = tbl.date
WHERE cal.date BETWEEN '2009-06-25' AND '2009-07-01';

You might want to create table cal instead of the subselect.