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

User · Answer

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.

User · Answer

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

User · Answer

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

User · Answer

You can use as an alternate solution   SELECT   FROM TABLE NAME WHERE  date   gt    1-jan-2013   OR  date   lt    12-jan-2013

User · Answer

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

User · Answer

you must add 1 day to the end date  using   DATE ADD   end date   INTERVAL 1 DAY

User · Answer

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

[mysql] Mysql: Select all data between two dates

Examples related to mysql

Examples related to date

Examples related to between