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

IF YOU CAN AVOID IT.. DON'T DO IT

Databases aren't really designed for this, you are effectively trying to create data (albeit a list of dates) within a query.

For anyone who has an application layer above the DB query the simplest solution is to fill in the blank data there.

You'll more than likely be looping through the query results anyway and can implement something like this:

loop_date = start_date

while (loop_date <= end_date){

  if(loop_date in db_data) {
    output db_data for loop_date
  }
  else {
    output default_data for loop_date
  }

  loop_date = loop_date + 1 day
}

The benefits of this are reduced data transmission; simpler, easier to debug queries; and no worry of over-flowing the calendar table.

Answer 2

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 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.

Answer 4

you must add 1 day to the end date, using: DATE_ADD('$end_date', INTERVAL 1 DAY)

Answer 5

Select *  from  emp where joindate between date1 and date2;

But this query not show proper data.

Eg

1-jan-2013 to 12-jan-2013.

But it's show data

1-jan-2013 to 11-jan-2013.

Answer 6

its very easy to handle this situation

You can use BETWEEN CLAUSE in combination with date_sub( now( ) , INTERVAL 30 DAY ) AND NOW( )

SELECT
    sc_cust_design.design_id as id,
    sc_cust_design.main_image,
    FROM
    sc_cust_design
WHERE
    sc_cust_design.publish = 1 
    AND **`datein`BETWEEN date_sub( now( ) , INTERVAL 30 DAY ) AND NOW( )**

Happy Coding :)