MYSQL - 当日期超过 1 年时更新行

Question

如果 DateTimeAdded 为 1 年或更长，我的查询可以更新该行。但我发现它在闰年不起作用。大家有更好的建议吗？

 CREATE EVENT UpdateProduct
     ON SCHEDULE
       EVERY 1 DAY
         DO

 update `product`  set Label = "ClearStock"  where  datediff(now(),
 DateTimeAdded) >= 365

Answer 1

要识别 DateTimeAdded 值超过一年的行，您可以执行以下操作:

 ... WHERE DateTimeAdded < NOW() + INTERVAL -1 YEAR

<小时/>

跟进

演示:

SELECT NOW() + INTERVAL 0 HOUR AS `now`
     , DATE(NOW()) + INTERVAL -1 YEAR AS `year_ago`
     , t.DateTimeAdded
     , t.DateTimeAdded < DATE(NOW()) + INTERVAL -1 YEAR AS `compare`
  FROM ( SELECT '2015-01-24 11:00:00' AS `DateTimeAdded`
          UNION ALL
         SELECT '2015-01-25 13:00:00'
          UNION ALL
         SELECT '2015-01-26 14:00:00'
       ) t  

返回:

NOW                  year_ago    DateTimeAdded        compare  
-------------------  ----------  -------------------  -------
2016-01-25 22:11:56  2015-01-25  2015-01-24 11:00:00        1
2016-01-25 22:11:56  2015-01-25  2015-01-25 13:00:00        0
2016-01-25 22:11:56  2015-01-26  2015-01-26 14:00:00        0