Looping through dates without table? 2

[Sleidia]

Hi

Is there a way to have mysql simply output all the dates between 2007-10-10 and 2007-10-15 without using a single table?

Resulting rows would be :

2007-10-10
2007-10-11
2007-10-12
2007-10-13
2007-10-14
2007-10-15

Thanks

 

[r937]

here's one way --

select date_add('2007-10-10', interval i day)
  from (
       select 0 as i
       union all select 1
       union all select 2
       union all select 3
       union all select 4
       union all select 5
       ) as d

r937.com | rudy.ca

 

[Sleidia]

Wow ... this is pure sweetness!

Thanks!

 

[r937]

i don't suppose you will like this very much then...

select date('2007-10-10') as thedate
union all select date('2007-10-11')
union all select date('2007-10-12') 
union all select date('2007-10-13')
union all select date('2007-10-14')
union all select date('2007-10-15')

r937.com | rudy.ca

 

[Sleidia]

ahaha ... err no thanks

Since the first query can be created dynamically by adding as many "union all select #" as needed in a PHP loop, it is the best.

 

[r937]

i'm a coldfusion programmer, i'd just generate the dates



you mentioned "without any tables"

may i ask why?

have you heard of the integers table?


r937.com | rudy.ca

 

[Sleidia]

Well, I could do it with PHP but I wasn't in the mood for PHP today

As for the why, I just wanted to know if it was possible.

And no, I never heard of "integers table" ( will google it later )

But I'm surprised there is no way to just use a starting date and an ending date to find the days between using soomething like "WHERE ... >= '2007-10-10' AND ... <= '2007-10-15'"
I guess that the WHERE clause can be used only on a existing table.

 

[r937]

but you can use a WHERE clause with derived tables

select thedate from (
select date_add('2007-10-10'
        , interval 10* one.i + two.i day) as thedate
  from (
       select 0 as i
       union all select 1
       union all select 2
       union all select 3
       union all select 4
       union all select 5
       ) as one
cross
  join (
       select 0 as i
       union all select 1
       union all select 2
       union all select 3
       union all select 4
       union all select 5
       ) as two
) as thirtysix
where thedate between '2007-10-10' and '2007-11-01'
order by thedate

note that the dates cross a month boundary



r937.com | rudy.ca

 

[Sleidia]

That's what happens when you live under a bad climate : you spend all days at home in front of a computer and then become a (mad) genius

As for the query, give me a couple of years before I can comment back