• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 902
  • Last Modified:

How to select date between dates...

Basically what I want to do is to be able to select all ofthe dates between 2 dates.

So say that I have 1 record each on March 23, March 24, March 25

but I select  dates between March 1 -March 25


I would like to get 25 results back, naturally there would be empty results on dates with no information.. So in my case above I would have 22 empty results and only 3 results with data in them.  All results would have their date on them however.


Does this make anysense?

An idea I have tossed around:
Using PHP to create a temporary table of dates, and joining that table on my query.


I figured it migh tbe possible to have mysql select all the dates though.. so I was holding off that doing the above solution.


 






0
aventure
Asked:
aventure
  • 4
1 Solution
 
todd_farmerCommented:
Sadly, you can't get this very easily.  You need to create a table with the 25 dates in them (or at least the numbers 1-25, which you can convert into dates using ADDDATE()) do a left join to the table.  You have the right idea (in your php code).  The alternative to creating a table with the dates is to use the numbers with ADDDATE().  This migth be better if you want to always search within a single month, for example.
0
 
todd_farmerCommented:
Here's an example of how to do it with a table of numeric values.  I prefer this mechanism because you can reuse it for any date range (as long as the range isn't greater than the number of rows in the table):

CREATE TABLE DA (i INT);
INSERT INTO DA VALUES (1);
INSERT INTO DA SELECT (SELECT MAX(i) FROM DA)+i FROM DA;
INSERT INTO DA SELECT (SELECT MAX(i) FROM DA)+i FROM DA;
INSERT INTO DA SELECT (SELECT MAX(i) FROM DA)+i FROM DA;
INSERT INTO DA SELECT (SELECT MAX(i) FROM DA)+i FROM DA;
INSERT INTO DA SELECT (SELECT MAX(i) FROM DA)+i FROM DA;
INSERT INTO DA VALUES (0);
DELETE FROM DA WHERE i > 31;
SELECT ADDDATE('2006-03-01', i) FROM DA WHERE ADDDATE('2006-03-01', i) <= CURDATE() ORDER BY 1;
0
 
todd_farmerCommented:
Here it is running.  Note that because the DA table can be reused to get any monthly date range, you don't have to use it in the context of a temporary table - just create and populate it once.

mysql> CREATE TABLE DA (i INT);
Query OK, 0 rows affected (0.14 sec)

mysql> INSERT INTO DA VALUES (1);
Query OK, 1 row affected (0.08 sec)

mysql> INSERT INTO DA SELECT (SELECT MAX(i) FROM DA)+i FROM DA;
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> INSERT INTO DA SELECT (SELECT MAX(i) FROM DA)+i FROM DA;
Query OK, 2 rows affected (0.05 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO DA SELECT (SELECT MAX(i) FROM DA)+i FROM DA;
Query OK, 4 rows affected (0.05 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> INSERT INTO DA SELECT (SELECT MAX(i) FROM DA)+i FROM DA;
Query OK, 8 rows affected (0.05 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> INSERT INTO DA SELECT (SELECT MAX(i) FROM DA)+i FROM DA;
Query OK, 16 rows affected (0.06 sec)
Records: 16  Duplicates: 0  Warnings: 0

mysql> INSERT INTO DA VALUES (0);
Query OK, 1 row affected (0.05 sec)

mysql> DELETE FROM DA WHERE i > 31;
Query OK, 1 row affected (0.05 sec)

mysql> SELECT ADDDATE('2006-03-01', i) FROM DA WHERE ADDDATE('2006-03-01', i) <= CURDATE() ORDER BY 1;
+--------------------------+
| ADDDATE('2006-03-01', i) |
+--------------------------+
| 2006-03-01               |
| 2006-03-02               |
| 2006-03-03               |
| 2006-03-04               |
| 2006-03-05               |
| 2006-03-06               |
| 2006-03-07               |
| 2006-03-08               |
| 2006-03-09               |
| 2006-03-10               |
| 2006-03-11               |
| 2006-03-12               |
| 2006-03-13               |
| 2006-03-14               |
| 2006-03-15               |
| 2006-03-16               |
| 2006-03-17               |
| 2006-03-18               |
| 2006-03-19               |
| 2006-03-20               |
| 2006-03-21               |
| 2006-03-22               |
| 2006-03-23               |
| 2006-03-24               |
| 2006-03-25               |
+--------------------------+
25 rows in set (0.00 sec)

mysql>
0
 
todd_farmerCommented:
Thanks for the points.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now