[RESOLVED] Fetch a range of dates, each in a distinct row

rheinen

How would I go about generating a column of incrementing dates when provided a given start and end date/number of days? e.g.:

2006-05-07
2006-05-08
2006-05-09
2006-05-10
2006-05-11
2006-05-12
2006-05-13
2006-05-14
...

These dates should be in seperate rows. I know I could create a table and then have PHP automatically populate it, but I would like to know if there is a way that I could do this with just MySQL. I've done some searching, but have had trouble locating exactly what I need. Ultimately I'm going to use this table to peform a join against another table that has a date field, and I want a seperate copy of each row for every day that it coincides with.

If anyone could offer some input I would greatly appreciate it.

Thanks,

Ryan

liquorvicar

Not quite sure what you're after but can't you achieve the same effect by using a GROUP BY clause in your query, rather than joining your query to a table of dates?

Sxooter

This tends to be very database specific.

For instance, in postgresql starting with version 8.0 you can do this with prodigious application of the generate_series function. Before that, not so simple. Not a clue how in mysql.

For postgresql 7.4 I wrote this VERY simple program to do that for me:

#!/usr/bin/php -q
create table pivot_dates (dt timestamp);
begin;
<?php
for ($i=0;$i<20000;$i++){
        print "insert into pivot_dates values ('2004-01-01 00:00:00'::timestamp +  interval '1 day'*$i);";
}
?>
commit;

Up to you to make it work in mysql...

rheinen

liquorvicar: Thanks for the feedback. Unfortunately no, I can't achieve it using a GROUP BY, at least as far as I can tell. If say one record has a start date of of 2006-01-01 and lasts 5 days, I need a seperate copy of that record for every day that it overlaps with the range that I am checking for.

Sxooter: Thanks as well for your response. That generate_series function sounds like exactly what I needed, but unfortunately in this case I'm constrained to MySQL.

I've decided just to populate a table in my database with dates from now until many years down the road (using a script very similar to what you posted Sxooter). When I want a specific range I will just use a subquery to pull out the rows I need, order them, and then join that against my other table.

Thanks everyone for your input.

Ryan