mysql - 选择日期在 X 个月内的位置，无论存储的年份如何

Question

我目前有一个查询，正在获取距离截止日期不到 3 个月的记录。 截止日期存储为日期，但年份并不重要，因为该记录每年都会标记。

我的查询:

SELECT client_name 
FROM client 
WHERE MOD(DAYOFYEAR(deadline) - DAYOFYEAR(CURDATE()), +365) <= 90 
AND DAYOFYEAR(deadline) > DAYOFYEAR(CURDATE())

如果我凭内存写这篇文章时出现语法错误，我深表歉意 - 但它确实有效。

它会一直工作到第一季度的截止日期并且当前日期是最后一个季度，然后它不再返回记录。我该如何解决这个问题？

因此查询需要返回截止日期在当前日期起 3 个月内的记录。应忽略deadline 日期中的年份，因为这可能是几年前，但重要的是deadline 的日期和月份。

或者是我存储的日期有问题？我应该每年更新一次吗？

谢谢

Answer 1

一种方法是使用如下的条件测试:

WHERE CONCAT(YEAR(NOW()),DATE_FORMAT(d.deadline,'-%m-%d')) 
        + INTERVAL CONCAT(YEAR(NOW()),DATE_FORMAT(d.deadline,'-%m-%d'))<NOW() YEAR
    < NOW() + INTERVAL 3 MONTH

我们可以稍微解压一下。在第一行，我们通过采用当前年份并附加截止日期中的月份和日期值来创建“下一个到期”截止日期。

但是有一个问题。其中一些“下一个截止日期”已经过去了。因此，为了解决这个问题(当 3 个月的期限“换算”到下一年时)，我们需要在当前日期之前的任何“下一个到期”截止日期添加一年。 p>

现在，我们可以将其与 3 个月后的日期进行比较，以确定“下一个到期”截止日期是否在接下来的 3 个月内。

这有点复杂。

这里有一个 SQL Fiddle 作为演示:http://sqlfiddle.com/#!2/c90e9/3 .

对于测试来说，NOW() 很不方便，因为它总是返回今天的日期。因此，为了进行测试，我们将所有出现的 NOW() 替换为用户定义的变量 @now，并将其设置为不同的日期，这样我们就可以进行适当的测试。

这是我用于测试的 SQL 语句。第一个表达式是我们计划在 WHERE 子句中使用的条件测试。为了进行测试，我们希望返回所有行，然后查看哪些行被 due_in_3mo 标记为 TRUE (1)，哪些行被标记为FALSE (0)。

SELECT 列表中的第二个表达式只是“下一个到期”截止日期，与第一个表达式中使用的相同。

其余的表达式非常不言自明......我们还想显示我们正在比较的 future 3 个月的日期，以及原始的“截止日期”日期值。

SELECT 
       CONCAT(YEAR(@now),DATE_FORMAT(d.deadline,'-%m-%d')) 
       + INTERVAL CONCAT(YEAR(@now),DATE_FORMAT(d.deadline,'-%m-%d'))<@now YEAR 
       < @now + INTERVAL 3 MONTH   
    AS `due_in_3mo`

     , CONCAT(YEAR(@now),DATE_FORMAT(d.deadline,'-%m-%d')) 
       + INTERVAL CONCAT(YEAR(@now),DATE_FORMAT(d.deadline,'-%m-%d'))<@now YEAR 
    AS `next_due`

     , d.id
     , d.deadline + INTERVAL 0 DAY AS `deadline`
     , @now + INTERVAL 3 MONTH AS `now+3mo`
     , @now + INTERVAL 0 DAY AS `now`
  FROM d d
 CROSS
  JOIN (SELECT @now := '2015-11-01') i
 ORDER BY d.id

更改内联 View 中分配给 @now 的值(别名为 i)，以使用其他日期值进行测试。

(您可能需要使用 DATE(NOW()) 代替 NOW()，这样时间就不会混合在一起，并且您可能想要从中减去另一天，这实际上取决于您希望如何处理截止日期的边缘情况，月份和日期与当前日期相同。(即，您是否想将其视为“过去”。)

总结该方法:将“下一个到期”截止日期生成为 future 的 DATE 值，并与现在 3 个月后的日期进行比较。