# Dynamically return a range of dates?

#### Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

•  Subject
• Author
• Posted on
I can retrieve today's date:

mysql> SELECT CURDATE() AS begin;
+------------+
| begin      |
+------------+
| 2005-06-01 |
+------------+
1 row in set (0.00 sec)

I can retrieve a date 3 days from now:

mysql> SELECT DATE_ADD(CURDATE(), INTERVAL 3 DAY) AS end;
+------------+
| end        |
+------------+
| 2005-06-04 |
+------------+
1 row in set (0.00 sec)

How do retrieve the range?

+------------+
| range      |
+------------+
| 2005-06-01 |
+------------+
| 2005-06-02 |
+------------+
| 2005-06-03 |
+------------+
| 2005-06-04 |
+------------+
4 row in set (0.00 sec)

## Re: Dynamically return a range of dates?

Jorey Bump (devnull@joreybump.com) wrote:
: I can retrieve today's date:

: mysql> SELECT CURDATE() AS begin;
: +------------+
: | begin      |
: +------------+
: | 2005-06-01 |
: +------------+
: 1 row in set (0.00 sec)

: I can retrieve a date 3 days from now:

: mysql> SELECT DATE_ADD(CURDATE(), INTERVAL 3 DAY) AS end;
: +------------+
: | end        |
: +------------+
: | 2005-06-04 |
: +------------+
: 1 row in set (0.00 sec)

: How do retrieve the range?

: +------------+
: | range      |
: +------------+
: | 2005-06-01 |
: +------------+
: | 2005-06-02 |
: +------------+
: | 2005-06-03 |
: +------------+
: | 2005-06-04 |
: +------------+
: 4 row in set (0.00 sec)

one technique

create table my_list (  I int );

insert into my_list values (1);
insert into my_list values (2);
insert into my_list values (3);
(etc)

select    DATE_ADD(CURDATE(), INTERVAL I day) as end
from    my_list
where    i between 1 and 4;

--

This space not for rent.

## Re: Dynamically return a range of dates?

yf110@vtn1.victoria.tc.ca (Malcolm Dew-Jones) wrote in news:429e1453
@news.victoria.tc.ca:

It's this part that I'm trying to avoid. I'm already using an
intermediate table that stores a range of dates and related info (name of
weekday, other formats, etc.). Since I can get the related info with a
simple query using a date function, I could reduce the table to a single
column of dates. But I'd like to go one step further and simply generate
the dates dynamically with a single query, given a start and end date.
Then I wouldn't need an intermediate table to get the dates for all
Mondays in a given time period, for example.

As a minimalist, I'll admit that's pretty cool. :) My application is only
concerned about dates relative to today, so this is a step closer to what
I want. Thanks.

## Re: Dynamically return a range of dates?

Jorey Bump (devnull@joreybump.com) wrote:
: yf110@vtn1.victoria.tc.ca (Malcolm Dew-Jones) wrote in news:429e1453
: @news.victoria.tc.ca:

: > Jorey Bump (devnull@joreybump.com) wrote:
: >: I can retrieve today's date:
: >
: >: mysql> SELECT CURDATE() AS begin;
: >: +------------+
: >: | begin      |
: >: +------------+
: >: | 2005-06-01 |
: >: +------------+
: >: 1 row in set (0.00 sec)
: >
: >: I can retrieve a date 3 days from now:
: >
: >: mysql> SELECT DATE_ADD(CURDATE(), INTERVAL 3 DAY) AS end;
: >: +------------+
: >: | end        |
: >: +------------+
: >: | 2005-06-04 |
: >: +------------+
: >: 1 row in set (0.00 sec)
: >
: >: How do retrieve the range?
: >
: >: +------------+
: >: | range      |
: >: +------------+
: >: | 2005-06-01 |
: >: +------------+
: >: | 2005-06-02 |
: >: +------------+
: >: | 2005-06-03 |
: >: +------------+
: >: | 2005-06-04 |
: >: +------------+
: >: 4 row in set (0.00 sec)
: >
: >
: > one technique
: >
: >
: >      create table my_list (  I int );
: >
: >      insert into my_list values (1);
: >      insert into my_list values (2);
: >      insert into my_list values (3);
: >           (etc)

: It's this part that I'm trying to avoid. I'm already using an
: intermediate table that stores a range of dates and related info (name of
: weekday, other formats, etc.). Since I can get the related info with a
: simple query using a date function, I could reduce the table to a single
: column of dates. But I'd like to go one step further and simply generate
: the dates dynamically with a single query, given a start and end date.
: Then I wouldn't need an intermediate table to get the dates for all
: Mondays in a given time period, for example.

: >      select     DATE_ADD(CURDATE(), INTERVAL I day) as end
: >      from     my_list
: >      where     i between 1 and 4;

: As a minimalist, I'll admit that's pretty cool. :) My application is only
: concerned about dates relative to today, so this is a step closer to what
: I want. Thanks.

my_list would be created once with enough rows for your largest query and
then left in place (i.e. it is not a temporary table). The where clause is
used to select the number of rows (or limit).

Alternatively...
...if you have a table with enough rows then use that instead.  mysql
doesn't have a rownum, but the following trick is based on a post by a guy
named Jeff Cann (thanks Jeff!).

E.g. I have a table named Files that has five rows, so I can use that to
get a list of five numbers

SET @rownum := 0;

select DATE_ADD(CURDATE(), INTERVAL @rownum := @rownum+1 day) as day
from Files;

+------------+
| day        |
+------------+
| 2005-06-02 |
| 2005-06-03 |
| 2005-06-04 |
| 2005-06-05 |
| 2005-06-06 |
+------------+
5 rows in set (0.00 sec)

This needs limit to control the number of rows (up to the number of rows
available in the table).

--

This space not for rent.

## Re: Dynamically return a range of dates?

Jorey Bump wrote: