aventure
asked on
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.
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.
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.
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;
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;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Thanks for the points.