---snip--
> Given any two dates, MySQL can tell if a third date is within
> that range.
> That's easy.
>
> To actually return a list of all dates between any arbitrary
> pair of dates requires some form of loop (v5.0+) or a lookup
> into a table populated with all possible dates (any version
> that supports joins). It's possible to get MySQL to give you
> a list of dates but not as a "native" function. There is just
> no facility built into the system to return that list. Sorry!
> Have you seen such a function before? If so, where and what
> was it called?
>
> Most of the times when people ask this question, they have a
> report they want to write and need to generate "blank" rows
> for dates that aren't in the data. Is that what you need or
> is there some other purpose to your question?
>
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
Indeed, we were asked this question on the freenode IRC channel a few weeks
ago - somebody wanted to calculate how many days between two dates fell
within a weekend, where there were possibly gaps within the data that they
had. I came up with the following procedure which gives an example of how to
do it with a loop and a temporary table, then some other little date
statistics for the given date range. The main point of interest, as Shawn
noted, is the loop that creates the dates within the range, and inserts them
in to a temporary table:
DROP PROCEDURE date_stats//
CREATE PROCEDURE date_stats ( IN sdate DATE, IN edate DATE)
BEGIN
DECLARE dates_done INT DEFAULT 0;
CREATE TEMPORARY TABLE date_range ( tdate DATE );
dates: LOOP
IF dates_done = 1 THEN
LEAVE dates;
END IF;
CASE WHEN sdate < edate
THEN INSERT INTO date_range VALUES (sdate);
ELSE SET dates_done = 1;
END CASE;
SET sdate = sdate + INTERVAL 1 DAY;
END LOOP dates;
SELECT SUM(IF(WEEKDAY(tdate) IN (0,1,2,3,4),1,0)) as week_day_cnt,
SUM(IF(WEEKDAY(tdate) IN (5,6),1,0)) as weekend_day_cnt
FROM date_range;
SELECT ROUND(COUNT(*)/7) as number_of_weeks,
COUNT(*) as number_of_days
FROM date_range;
SELECT TIMESTAMPDIFF(SECOND,MIN(tdate),MAX(tdate)) as seconds_diff,
TIMESTAMPDIFF(MINUTE,MIN(tdate),MAX(tdate)) as minutes_diff,
TIMESTAMPDIFF(HOUR,MIN(tdate),MAX(tdate)) as hours_diff
FROM date_range;
DROP TEMPORARY TABLE date_range;
END;
//
CALL date_stats('2005-01-01','2005-02-01')//
+--------------+-----------------+
| week_day_cnt | weekend_day_cnt |
+--------------+-----------------+
| 21 | 10 |
+--------------+-----------------+
1 row in set (2.78 sec)
+-----------------+----------------+
| number_of_weeks | number_of_days |
+-----------------+----------------+
| 4 | 31 |
+-----------------+----------------+
1 row in set (2.78 sec)
+--------------+--------------+------------+
| seconds_diff | minutes_diff | hours_diff |
+--------------+--------------+------------+
| 2592000 | 43200 | 720 |
+--------------+--------------+------------+
1 row in set (2.78 sec)
Query OK, 0 rows affected (2.97 sec)
Hope this helps,
Mark
Mark Leith
Cool-Tools UK Limited
http://www.cool-tools.co.uk
http://leithal.cool-tools.co.uk
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.344 / Virus Database: 267.10.18/91 - Release Date: 06/09/2005
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
