mysql - 将 DATE 字段的范围与 MySQL 中的 CURDATE() 进行比较，而不考虑年份

Question

我有一个表 closures，其中包含我客户的业务关闭的时间:

+----+------------+------------+-------------------------------+--------+
| id | start_date | end_date   | reason                        | yearly |
+----+------------+------------+-------------------------------+--------+
|  1 | 2010-12-24 | 2011-01-08 | Holidays                      | 1      |
|  3 | 2011-01-03 | 2011-01-10 | testing purposes              | 1      |
|  5 | 2001-01-01 | 2001-01-10 | A non-yearly event            | 0      |
+----+------------+------------+-------------------------------+--------+

我一直在尝试制定一个查询来检查 closures 中是否有任何行，其中 CURDATE() 位于 start_date 和 end_date。关键是弄清楚如果该行标记为 yearly = '1'，如何忽略年份。这是我到目前为止得到的:

SELECT `id`,`reason`,`start_date`,`end_date`,`yearly` FROM `closures` 
WHERE ( CURDATE() BETWEEN `start_date` AND `end_date` ) /* full date with year is compared */
OR 
(
    `yearly` = '1' /* look for events marked yearly */
    AND
    (
        ( RIGHT( CURDATE() , 5 ) BETWEEN RIGHT( `start_date` , 5 ) AND RIGHT( `end_date` , 5 ) ) /* This WORKS for all date ranges marked yearly that do not go through new years */
        OR
        ( RIGHT( CURDATE() , 5 ) >= RIGHT( `start_date` , 5 ) AND RIGHT( `start_date` , 5 ) > RIGHT( `end_date` , 5 ) ) /* This DOES NOT catch date ranges that go through new years */
    )
)

查询上面显示的表格，我得到这个:

+----+------------------+------------+------------+--------+
| id | reason           | start_date | end_date   | yearly |
+----+------------------+------------+------------+--------+
|  3 | testing purposes | 2011-01-03 | 2011-01-10 | 1      |
+----+------------------+------------+------------+--------+

当我期望得到这个时:

+----+------------------+------------+------------+--------+
| id | reason           | start_date | end_date   | yearly |
+----+------------------+------------+------------+--------+
|  1 | Holidays         | 2010-12-24 | 2011-01-08 | 1      |
+----+------------------+------------+------------+--------+
|  3 | testing purposes | 2011-01-03 | 2011-01-10 | 1      |
+----+------------------+------------+------------+--------+

我走在正确的轨道上吗？有没有比使用 RIGHT() 更好/更快的检查 DATE 值的 MM-DD 的方法？

帮帮我吧，我不配...

Answer 1

SELECT * FROM closures
WHERE (
CURDATE() BETWEEN
  IF(yearly=0,start_date,date_add(start_date, INTERVAL year(CURDATE())-year(start_date) YEAR))
AND
  IF(yearly=0,end_date,date_add(date_add(start_date, INTERVAL year(CURDATE())-year(start_date) YEAR), INTERVAL datediff(end_date, start_date) DAY))
) OR (
CURDATE() BETWEEN
  IF(yearly=0,start_date,date_add(date_add(end_date, INTERVAL year(CURDATE())-year(end_date) YEAR), INTERVAL -1*datediff(end_date, start_date) DAY))
AND
  IF(yearly=0,end_date,date_add(end_date, INTERVAL year(CURDATE())-year(end_date) YEAR))
)

应该可以解决问题。岩石边缘是年份变化，所以我计算每次关闭的天数长度，1. 从当年相应的开始日期加上它，2. 从当年相应的结束日期减去它。