比较日期范围

Question

在MySQL中，如果我有一个日期范围列表(范围-开始和范围-结束)。G.10/06/1983 to 14/06/198315/07/1983 to 16/07/198318/07/1983 to 18/07/1983我想检查另一个日期范围是否包含列表中的任何一个范围，我将如何做到这一点？G.06/06/1983 to 18/06/1983 = IN LIST10/06/1983 to 11/06/1983 = IN LIST14/07/1983 to 14/07/1983 = NOT IN LIST

Answer 1

这是一个经典的问题，如果你逆转逻辑，它实际上会更容易。

让我举个例子。

我将在这里张贴一段时间，以及所有其他时期的不同变化，以某种方式重叠。

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

另一方面，让我发布所有不重叠的内容：

           |-------------------|          compare to this one
     |---|                                ends before
                                 |---|    starts after

因此，如果简单地将比较简化为：

starts after end
ends before start

然后你会找到所有不重叠的，然后你会找到所有不匹配的周期。

对于最后一个不是在列表中的例子，您可以看到它与这两个规则相匹配。

您需要确定下列期间是否在您的范围内或范围之外：

           |-------------|
   |-------|                       equal end with start of comparison period
                         |-----|   equal start with end of comparison period

如果表中有名为range_end和range_start的列，下面提供一些简单的SQL来检索所有匹配的行：

SELECT *FROM periodsWHERE NOT (range_start > @check_period_end           OR range_end < @check_period_start)

注意不在里面。因为这两个简单的规则可以找到所有的非匹配行，一个简单的不一定会倒过来说：如果它不是不匹配的行之一，则必须是匹配的行之一。.

在这里应用简单的反转逻辑来消除NOT，您将得到以下结果：

SELECT *FROM periodsWHERE range_start <= @check_period_end      AND range_end >= @check_period_start

Answer 2

以您的示例范围为06/06/1983至18/06/1983，并假设您的列名为启动和端部对于您的范围，您可以使用这样的子句

where ('1983-06-06' <= end) and ('1983-06-18' >= start)

例如，检查测试范围的开始在数据库范围结束之前，测试范围的结束在数据库范围开始后或开始时。

Answer 3

我创建了函数来处理MySQL中的这个问题。只需将日期转换为使用前的秒。

DELIMITER ;;CREATE FUNCTION overlap_interval(x INT,y INT,a INT,b INT)RETURNS INTEGER DETERMINISTICBEGINDECLARE
    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 ;