---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]