MySQL 比较日期范围
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/143552/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Comparing date ranges
提问by Kieran Benton
In MySQL, If I have a list of date ranges (range-start and range-end). e.g.
在 MySQL 中，如果我有一个日期范围列表（范围开始和范围结束）。例如
10/06/1983 to 14/06/1983
15/07/1983 to 16/07/1983
18/07/1983 to 18/07/1983
And I want to check if another date range contains ANY of the ranges already in the list, how would I do that?
我想检查另一个日期范围是否包含列表中已有的任何范围，我该怎么做？
e.g.
例如
06/06/1983 to 18/06/1983 = IN LIST
10/06/1983 to 11/06/1983 = IN LIST
14/07/1983 to 14/07/1983 = NOT IN LIST
回答by Lasse V. Karlsen
This is a classical problem, and it's actually easier if you reverse the logic.
这是一个经典的问题，如果你颠倒逻辑，它实际上更容易。
Let me give you an example.
让我给你举个例子。
I'll post one period of time here, and all the different variations of other periods that overlap in some way.
我将在这里发布一个时期，以及以某种方式重叠的其他时期的所有不同变化。
|-------------------| compare to this one
|---------| contained within
|----------| contained within, equal start
|-----------| contained within, equal end
|-------------------| contained within, equal start+end
|------------| not fully contained, overlaps start
|---------------| not fully contained, overlaps end
|-------------------------| overlaps start, bigger
|-----------------------| overlaps end, bigger
|------------------------------| overlaps entire period
on the other hand, let me post all those that doesn't overlap:
另一方面，让我发布所有不重叠的内容：
|-------------------| compare to this one
|---| ends before
|---| starts after
So if you simple reduce the comparison to:
因此，如果您简单地将比较减少到：
starts after end
ends before start
then you'll find all those that doesn't overlap, and then you'll find all the non-matching periods.
然后您会找到所有不重叠的时间段，然后您会找到所有不匹配的时间段。
For your final NOT IN LIST example, you can see that it matches those two rules.
对于最后的 NOT IN LIST 示例，您可以看到它与这两个规则匹配。
You will need to decide wether the following periods are IN or OUTSIDE your ranges:
您需要决定以下时期是在您的范围内还是在您的范围之外：
|-------------|
|-------| equal end with start of comparison period
|-----| equal start with end of comparison period
If your table has columns called range_end and range_start, here's some simple SQL to retrieve all the matching rows:
如果您的表有名为 range_end 和 range_start 的列，这里有一些简单的 SQL 来检索所有匹配的行：
SELECT *
FROM periods
WHERE NOT (range_start > @check_period_end
OR range_end < @check_period_start)
Note the NOTin there. Since the two simple rules finds all the non-matchingrows, a simple NOT will reverse it to say: if it's not one of the non-matching rows, it has to be one of the matching ones.
注意那里的NOT。由于这两个简单的规则会找到所有不匹配的行，一个简单的 NOT 会将其反转为：如果它不是不匹配的行之一，则它必须是匹配的行之一。
Applying simple reversal logic here to get rid of the NOT and you'll end up with:
在这里应用简单的反转逻辑来摆脱 NOT，你最终会得到：
SELECT *
FROM periods
WHERE range_start <= @check_period_end
AND range_end >= @check_period_start
回答by Paul Dixon
Taking your example range of 06/06/1983 to 18/06/1983 and assuming you have columns called startand endfor your ranges, you could use a clause like this
以您的示例范围 06/06/1983 到 18/06/1983 并假设您的范围有名为start和end 的列，您可以使用这样的子句
where ('1983-06-06' <= end) and ('1983-06-18' >= start)
i.e. check the start of your test range is before the end of the database range, and that the end of your test range is after or on the start of the database range.
即检查您的测试范围的开始是在数据库范围的结束之前，并且您的测试范围的结束是在数据库范围的开始之后或开始。
回答by David Aldridge
If your RDBMS supports the OVERLAP() function then this becomes trivial -- no need for homegrown solutions. (In Oracle it apparantly works but is undocumented).
如果您的 RDBMS 支持 OVERLAP() 函数，那么这将变得微不足道——不需要自行开发的解决方案。（在 Oracle 中它显然可以工作，但没有记录）。
回答by onedaywhen
In your expected results you say
在您的预期结果中，您说
06/06/1983 to 18/06/1983 = IN LIST
06/06/1983 至 18/06/1983 = 在列表中
However, this period does not contain nor is contained by any of the periods in your table (not list!) of periods. It does, however, overlap the period 10/06/1983 to 14/06/1983.
但是，此期间不包含也不包含在您的期间表（不是列表！）中的任何期间。但是，它确实与 10/06/1983 至 14/06/1983 期间重叠。
You may find the Snodgrass book (http://www.cs.arizona.edu/people/rts/tdbbook.pdf) useful: it pre-dates mysql but the concept of time hasn't changed ;-)
您可能会发现 Snodgrass 的书 ( http://www.cs.arizona.edu/people/rts/tdbbook.pdf) 很有用：它早于 mysql，但时间的概念没有改变；-)
回答by jonavon
I created function to deal with this problem in MySQL. Just convert the dates to seconds before use.
我创建了函数来处理 MySQL 中的这个问题。只需在使用前将日期转换为秒。
DELIMITER ;;
CREATE FUNCTION overlap_interval(x INT,y INT,a INT,b INT)
RETURNS INTEGER DETERMINISTIC
BEGIN
DECLARE
overlap_amount INTEGER;
IF (((x <= a) AND (a < y)) OR ((x < b) AND (b <= y)) OR (a < x AND y < b)) THEN
IF (x < a) THEN
IF (y < b) THEN
SET overlap_amount = y - a;
ELSE
SET overlap_amount = b - a;
END IF;
ELSE
IF (y < b) THEN
SET overlap_amount = y - x;
ELSE
SET overlap_amount = b - x;
END IF;
END IF;
ELSE
SET overlap_amount = 0;
END IF;
RETURN overlap_amount;
END ;;
DELIMITER ;
回答by Rama Subba Reddy M
Look into the following example. It will helpful for you.
看看下面的例子。它会对你有帮助。
SELECT DISTINCT RelatedTo,CAST(NotificationContent as nvarchar(max)) as NotificationContent,
ID,
Url,
NotificationPrefix,
NotificationDate
FROM NotificationMaster as nfm
inner join NotificationSettingsSubscriptionLog as nfl on nfm.NotificationDate between nfl.LastSubscribedDate and isnull(nfl.LastUnSubscribedDate,GETDATE())
where ID not in(SELECT NotificationID from removednotificationsmaster where Userid=@userid) and nfl.UserId = @userid and nfl.RelatedSettingColumn = RelatedTo
回答by Paul Williamson
CREATE FUNCTION overlap_date(s DATE, e DATE, a DATE, b DATE)
RETURNS BOOLEAN DETERMINISTIC
RETURN s BETWEEN a AND b or e BETWEEN a and b or a BETWEEN s and e;
回答by RickyS
Try This on MS SQL
在 MS SQL 上试试这个
WITH date_range (calc_date) AS (
SELECT DATEADD(DAY, DATEDIFF(DAY, 0, [ending date]) - DATEDIFF(DAY, [start date], [ending date]), 0)
UNION ALL SELECT DATEADD(DAY, 1, calc_date)
FROM date_range
WHERE DATEADD(DAY, 1, calc_date) <= [ending date])
SELECT P.[fieldstartdate], P.[fieldenddate]
FROM date_range R JOIN [yourBaseTable] P on Convert(date, R.calc_date) BETWEEN convert(date, P.[fieldstartdate]) and convert(date, P.[fieldenddate])
GROUP BY P.[fieldstartdate], P.[fieldenddate];
回答by Florian HENRY - ATM Consulting
Another method by using BETWEEN sql statement
使用 BETWEEN sql 语句的另一种方法
Periods included :
期间包括：
SELECT *
FROM periods
WHERE @check_period_start BETWEEN range_start AND range_end
AND @check_period_end BETWEEN range_start AND range_end
Periods excluded :
排除期间：
SELECT *
FROM periods
WHERE (@check_period_start NOT BETWEEN range_start AND range_end
OR @check_period_end NOT BETWEEN range_start AND range_end)
回答by Gio
SELECT *
FROM tabla a
WHERE ( @Fini <= a.dFechaFin AND @Ffin >= a.dFechaIni )
AND ( (@Fini >= a.dFechaIni AND @Ffin <= a.dFechaFin) OR (@Fini >= a.dFechaIni AND @Ffin >= a.dFechaFin) OR (a.dFechaIni>=@Fini AND a.dFechaFin <=@Ffin) OR
(a.dFechaIni>=@Fini AND a.dFechaFin >=@Ffin) )
