日期在两个日期之间，忽略年份

Question

如何独立于年份比较日期是否在日期范围内？

表 "dates"：

    some_column| since        | upto        |  
    -----------|--------------|-------------|
   'foo'       | '2011-05-01' | '2013-06-01'|

现在我希望这个查询返回 'foo'

SELECT foo FROM dates WHERE '2014-05-05' BETWEEN `since` AND `upto`

如果需要的话，我可以更改"dates"表中存储日期的类型/格式，但我不能更改放入查询中的日期格式，因为该值通常来自另一个表（它是使用连接的更复杂查询的一部分）。

- gadelat

---

目前它们存储在日期类型中。我只是想说，如果需要的话，我可以更改它。 - gadelat

日期不是“格式”。它是一种类型。 - Mitch Wheat

好的，你抓住了我的意思。那就是我想表达的。 - gadelat

你没有解释清楚你所说的“独立于年份”的意思。 - Matt Johnson-Pint

这可能有所帮助http://stackoverflow.com/questions/23446462/mysql-convert-date-to-same-date-of-current-year - andrew

Answer 1

SELECT foo FROM dates WHERE DATE_FORMAT('2014-01-15', "%m-%d") 
    BETWEEN DATE_FORMAT(`since`,"%m-%d") AND DATE_FORMAT(`upto`,"%m-%d")

这里是SQL FIDDLE演示的链接。

- vikrant singh

---

干得好，不错的解决方案。 - Jonathan

Answer 2

使用 DayOfYear 函数：

 SELECT foo FROM dates WHERE DayOfYear('2014-05-05') 
    BETWEEN DayOfYear(`since`) AND DayOfYear(`upto`)

- Larry Lustig

---

好想法。我也考虑过这个问题。但闰年会打乱它。3月1日在非闰年是第60天，但在闰年是第61天。 - Matt Johnson-Pint

如果MySQL没有自己调整这个，另一个选择是编写自己的Julianizer类型函数（MONTH（dt）* 100 + DAY（dt）应该可以）。 - Larry Lustig

我不清楚如何将那个Julianizer集成到您发布的查询中，请您能否澄清一下？ - gadelat

只需将 DayOfYear 替换为它：SELECT foo FROM dates WHERE MONTH('2014-05-05')*100 + DAY('2014-05-05') BETWEEN (MONTH(since)*100 + DAY(since)) AND (MONTH(upto)*100 + DAY(upto))。 - Larry Lustig

确实是个好主意，但你还需要调整一下，以适应since和upto不是同一年的情况，例如since是12月，而upto是1月。你的测试将选择1月至12月之间的日期，而不是从12月到1月之间的日期。 - Fenix Aoras

Answer 3

既然您表示在数据中存储任意年份没有问题，那么您可以继续使用 DATE 类型。然后只需要考虑如何查询。

• 使用固定的任意年份存储字段。您应该使用闰年以适应2月29日的可能性。2000年很好用。(0004不起作用，因为它对于MySQL的Date类型太小了。)

• 将您查询的任何值的年份替换为相同的年份。

• 考虑跨越一年结束并进入下一年的范围。要完全回答这个问题，您需要一个类似以下的查询:

  SET @dt = cast(CONCAT('2000',RIGHT(thesourcedatevalue,6)) as DATE);
  
  SELECT some_column FROM dates
  WHERE (since <= upto AND since <= @dt AND upto >= @dt) OR
        (since > upto AND (since <= @dt OR upto >= @dt))
  

  这里是演示的SQL Fiddle

• 为了提高性能，您应确保有一个包括since和upto字段的索引。

- Matt Johnson-Pint

---

a) 0004 不太小，适用于 MySQL 的日期类型。 b) 它不起作用，我甚至不知道它如何工作。最后一对括号中的条件没有意义。此外，我无法将“@dt”存储为固定年份。它可以是2000年，也可以是2014年，任何年份都可以。我已经多次说过，我可以更改表格中的格式/类型/年份，但不能更改它们所比较的日期。我希望你的建议包括将“@dt”的年份替换为2000年的方法。 - gadelat

我真的不确定你对其余部分的意思。你没有存储@dt - 那是你正在查询的参数。在查询之前，您应该能够轻松操作它。 - Matt Johnson-Pint

这是正确的。 二月将在该范围之外，因此应该失败。 三月至一月将从三月开始，并在下一年的一月结束。 四月将在范围内，并通过测试。 - Matt Johnson-Pint

我现在明白你所说的价值来自于连接结果。我会更新我的答案，向你展示如何在SQL中操作源值。 - Matt Johnson-Pint

已更新并稍微修改了公式。我最初认为可以省略第一个测试，但实际上不行。即使你采用其他答案中展示的“DayOfYear”或“MMDD”方法，仍然需要考虑跨越年末的范围。祝你好运！ :) - Matt Johnson-Pint

Answer 4

在快速浏览了谷歌和StackOverflow之后，我遇到了以下两个问题：

• 某些年份有1天以上（2月29日）
• 所要求的范围（since和upto）可以属于不同的年份，这使得使用DayOfYear()成为一个问题。这是我想出的SQL查询。

假设我们有一个日期'2014-05-05'来检查：

SELECT foo FROM `dates` WHERE
        (
    /*     --------    since and upto are from same year    --------    */
            YEAR(`since`) = YEAR(`date_fin`)
            AND (
                (
                    MONTH(`since`) < "05"
                    OR
                    (
                        MONTH(`since`) = "05"
                        AND
                        DAY(`since`) <= "05"
                    )
                ) AND (
                    MONTH(`date_fin`) < "05"
                    OR
                    (
                        MONTH(`date_fin`) = "05"
                        AND
                        DAY(`date_fin`) >= "05"
                    )
                )
            )
        )OR(
    /*     --------    since and upto are from different year    --------    */
            YEAR(`since`) <> YEAR(`date_fin`) AND (
                (
                    MONTH(`since`) < "05"
                    OR
                    (
                        MONTH(`since`) = "05"
                        AND
                        DAY(`since`) <= "05"
                    )
                ) OR (
                    MONTH(`date_fin`) > "05"
                    OR
                    (
                        MONTH(`date_fin`) = "05"
                        AND
                        DAY(`date_fin`) >= "05"
                    )
                )
            )
        )

- Fenix Aoras

---

我不认为这些是问题。dayOfYear 不关心 since 和 upto 是不同的年份，是的，有些年份有不同的天数，而 dayOfYear 应该已经考虑到了这一点。 - gadelat

Answer 5

我以以下方式解决了一个类似的问题：

SELECT foo FROM dates

WHERE   '0000-05-05'
BETWEEN SUBDATE(since, INTERVAL YEAR(since) YEAR)
AND     SUBDATE(upto, INTERVAL YEAR(since) YEAR)

OR      ADDDATE('0000-05-05', INTERVAL 1 YEAR)
BETWEEN SUBDATE(since, INTERVAL YEAR(since) YEAR)
AND     SUBDATE(upto, INTERVAL YEAR(since) YEAR)

首先，我们将所有日期重置为零，接下来我们在两个范围之间查找特定日期（也将其重置为零）。在下面的示例中，我们已经从外部源传递了“重置”日期，但这也可以在mysql中完成：

WHERE   SUBDATE('2014-05-05', INTERVAL YEAR('2014-05-05') YEAR)

- Alex