By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,255 Members | 2,773 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,255 IT Pros & Developers. It's quick & easy.

Dynamically return a range of dates?

P: n/a
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)

Jul 23 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Jorey Bump (de*****@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.
Jul 23 '05 #2

P: n/a
yf***@vtn1.victoria.tc.ca (Malcolm Dew-Jones) wrote in news:429e1453
@news.victoria.tc.ca:
Jorey Bump (de*****@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.

Jul 23 '05 #3

P: n/a
Jorey Bump (de*****@joreybump.com) wrote:
: yf***@vtn1.victoria.tc.ca (Malcolm Dew-Jones) wrote in news:429e1453
: @news.victoria.tc.ca:

: > Jorey Bump (de*****@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.
Jul 23 '05 #4

P: n/a
Jorey Bump wrote:
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.


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.
Jul 23 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.