MySQL Server Forum - Dynamically return a range of dates?

Author

Dynamically return a range of dates?

Jorey Bump

2005-06-01, 1:23 pm

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)

Malcolm Dew-Jones

2005-06-01, 8:23 pm

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(CURD
ATE(), INTERVAL I day) as end
from my_list
where i between 1 and 4;

--

This space not for rent.

Jorey Bump

2005-06-01, 8:23 pm

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.

Malcolm Dew-Jones

2005-06-01, 8:23 pm

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.

Bill Karwin

2005-06-02, 9:23 am

Jorey Bump wrote:
>
>
> 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.

How about this:

SELECT CURDATE() + INTERVAL D.I DAY AS `END`
FROM (SELECT 1 AS I UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS D;

(Requires MySQL 4.1 for the subquery.)

Regards,
Bill K.

Sponsored Links