RE: Date Ranges
---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]
Re: Date Ranges
At 20:47 +0100 9/6/05, Shaun wrote: Hi, Given two dates, can Mysql calculate and return all the dates that occur between them? No. Given two dates, MySQL can determine which of a set of already-existing dates stored in a table occur between them and return those. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date Ranges
Shaun [EMAIL PROTECTED] wrote on 09/06/2005 03:47:25 PM: Hi, Given two dates, can Mysql calculate and return all the dates that occur between them? Thanks for your advice. 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
Re: date ranges
I have a date range (start date and end date) supplied by the user and I also have information in a table that has start dates and end dates. I want to select everything in the table whose date range overlaps in any way with the date range given by the user. Well... if you were given a start date of 2003-01-01 and end date of 2003-01-31... select * from table_name where start_date between 2003-01-01 and 2003-01-31 or end_date between 2003-01-01 and 2003-01-31 Example: start_date = 2002-01-01 and end_date = 2003-02-01. Instead, if the four dates are s1, e1, s2, and e2 (s=start, e=end), we want: s1 = e2 /* first range can't start after second range ends */ AND s2 = e1 /* second range can't start after first range ends */ Okay, so s1,e1 are supplied by the user and s2,e2 are in your tables right? Maybe it's just too early in the morning for me... but I don't understand the problem anymore. s1 = e2 ... yeah that's what my original query does. e1 = s2 ... what? yeah... it's an end date.. it's gonna be great than a start date by nature. Are you trying to say that you don't want overlap anymore? You want the s2,e2 to be contained by s1,e1? I doubt that's what you want b/c you wouldn't have written the list... it's just a small and trivial alteration to my original query... select * from table_name where (start_date between 2003-01-01 and 2003-01-31 or end_date between 2003-01-01 and 2003-01-31) and start_date 2003-01-01 and end_date 2003-01-31 Sorry, I guess you're gonna have to be more specific. I could probably sit here for 30 minutes reading your email over and over to understand... but I would rather you be more clear on what you want. But then again, I need another cup of coffee in me yet... Matt. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: date ranges
Matt Gostick wrote: Sarah Heffron wrote: I have a date range (start date and end date) supplied by the user and I also have information in a table that has start dates and end dates. I want to select everything in the table whose date range overlaps in any way with the date range given by the user. Matt Gostick's proposed solution: Well... if you were given a start date of 2003-01-01 and end date of 2003-01-31... select * from table_name where start_date between 2003-01-01 and 2003-01-31 or end_date between 2003-01-01 and 2003-01-31 Bruce Feist's response: Example: start_date = 2002-01-01 and end_date = 2003-02-01. Instead, if the four dates are s1, e1, s2, and e2 (s=start, e=end), we want: s1 = e2 /* first range can't start after second range ends */ AND s2 = e1 /* second range can't start after first range ends */ Matt's response to Bruce's response: Okay, so s1,e1 are supplied by the user and s2,e2 are in your tables right? That's irrelevant to the form of the condition, but yes. s1 = e2 ... yeah that's what my original query does. e1 = s2 ... what? yeah... it's an end date.. it's gonna be great than a start date by nature. But it's an end date from another range. There's not necessarily any relationship between the two. Your original suggestion works if the two ranges partially overlap, but not if one is wholly contained in the other. Bruce Feist -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: date ranges
Bruce Feist wrote: Instead, if the four dates are s1, e1, s2, and e2 (s=start, e=end), we want: s1 = e2 /* first range can't start after second range ends */ AND s2 = e1 /* second range can't start after first range ends */ Bruce Feist also wrote: Your original suggestion works if the two ranges partially overlap, but not if one is wholly contained in the other. Jones, Clifton R [CC] wrote: Not true... a pencil and paper excercise shows it does, indeed, work. What you quoted above wasn't Matt's original suggestion; it was my suggested alternative based on my above comments. So, yes, I believe it works. The original, and a counterexample I came up with for it, is in an earlier post. Bruce Feist -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: date ranges
Well... if you were given a start date of 2003-01-01 and end date of 2003-01-31... select * from table_name where start_date between 2003-01-01 and 2003-01-31 or end_date between 2003-01-01 and 2003-01-31 No? Matt. On Wed, 2003-04-02 at 16:32, Sarah Heffron wrote: How would I do this: I have a date range (start date and end date) supplied by the user and I also have information in a table that has start dates and end dates. I want to select everything in the table whose date range overlaps in any way with the date range given by the user. Thanks, Sarah __ Do you Yahoo!? Yahoo! Tax Center - File online, calculators, forms, and more http://tax.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: date ranges
Matt Gostick wrote: Well... if you were given a start date of 2003-01-01 and end date of 2003-01-31... select * from table_name where start_date between 2003-01-01 and 2003-01-31 or end_date between 2003-01-01 and 2003-01-31 No? No. Example: start_date = 2002-01-01 and end_date = 2003-02-01. Instead, if the four dates are s1, e1, s2, and e2 (s=start, e=end), we want: s1 = e2 /* first range can't start after second range ends */ AND s2 = e1 /* second range can't start after first range ends */ Bruce Feist Matt. On Wed, 2003-04-02 at 16:32, Sarah Heffron wrote: How would I do this: I have a date range (start date and end date) supplied by the user and I also have information in a table that has start dates and end dates. I want to select everything in the table whose date range overlaps in any way with the date range given by the user. Thanks, Sarah __ Do you Yahoo!? Yahoo! Tax Center - File online, calculators, forms, and more http://tax.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
