比较日期范围 [英] Comparing date ranges

问题描述

在 MySQL 中，如果我有一个日期范围列表(范围开始和范围结束).例如

In MySQL, If I have a list of date ranges (range-start and range-end). e.g.

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?

例如

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

推荐答案

这是一个经典的问题，如果你把逻辑颠倒一下，它实际上更容易.

This is a classical problem, and it's actually easier if you reverse the logic.

让我举个例子.

我将在这里发布一个时期，以及以某种方式重叠的其他时期的所有不同变化.

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.

对于最后一个 NOT IN LIST 示例，您可以看到它与这两个规则匹配.

For your final NOT IN LIST example, you can see that it matches those two rules.

您需要决定以下时间段是在您的范围内还是在您的范围之外:

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

如果您的表有名为 range_end 和 range_start 的列，这里有一些简单的 SQL 来检索所有匹配的行:

If your table has columns called range_end and range_start, here's some simple SQL to retrieve all the matching rows:

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

注意那里的 NOT.由于这两个简单的规则会找到所有非匹配行，一个简单的 NOT 会将其反转为:如果它不是非匹配行之一，它必须是其中之一匹配的.

Note the NOT in there. Since the two simple rules finds all the non-matching rows, 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，你最终会得到:

Applying simple reversal logic here to get rid of the NOT and you'll end up with:

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

这篇关于比较日期范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！