Mysql: select all data between two dates

Question

I have a mysql table with dates related data. Each row has data and a date, for example:

2009-06-25 75 2009-07-01 100 2009-07-02 120 

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

SELECT data FROM tbl WHERE date BETWEEN date1 AND date2

My problem is that I also need to get strings between date1 and date2, even if there is no data during the day.

Thus, my query will skip dates that were empty between 2009-06-25 and 2009-07-01.

Can I somehow add these dates with only 0 as data?

Answer 1

You can use the concept, which is often referred to as "calendar tables." Here is a good guide to creating 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 ai * 10 + bi DAY as date FROM ints a JOIN ints b ORDER BY ai * 10 + bi ) 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 a cal table instead of a subquery.

Answer 2

 Select * from emp where joindate between date1 and date2; 

But this query does not show the correct data.

For example,

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

But it shows data

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

Answer 3

its very easy to deal with this situation

You can use INTERNATIONAL CLAUSE in conjunction 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 :)

Answer 4

Do you have a table with all dates? If not, you might consider embedding a calendar table and shifting your data into a calendar table.

Answer 5

IF YOU CAN AVOID IT. DO NOT DO IT

Databases are not really designed for this, you are actually trying to create data (albeit a list of dates) in the query.

For those who have an application level above the database query, the easiest solution is to fill in empty data.

You will most likely cycle through the query results 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 transfer; simpler, easier to debug queries; and don’t worry about overflowing the calendar table.

Answer 6

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

Answer 7

You can use as an alternative solution:

 SELECT * FROM TABLE_NAME WHERE 'date' >= '1-jan-2013' OR 'date' <= '12-jan-2013'