发现在MySql中n天内是否有周年纪念日 [英] finding if an anniversary is coming up in n days in MySql

问题描述

我有一个周年纪念日的表。我想要一个查询，返回我在未来10天内出现的周年纪念列。例如：

  birthdate 
 --------- 
 1965-10-10 
 1982-05-25 
 
 
 SELECT birthdate FROM Anniversaries WHERE mystical_magical_mumbo_jumbo< = 10 
 
 + ---------- -  + 
 |出生日期
 + ------------ + 
 | 1982-05-25 | 
 + ------------ + 
 1行集（0.01秒）
  

我想以 x <= 10 的格式保留查询，因为我将在其他部分使用该数字10的查询，如果我将其设置为变量，我可以通过更改变量来改变它，而不必重新编写查询。

解决方案

正如其他人所说，你需要忽略这一年的比较。 DAYOFYEAR（）函数是一种方法。

这是一个快速的解决方案。它将在未来10天内返回所有生日，即使是12月下旬，明年的生日。

它不能正确处理闰年，所以它将如果今年是闰年，该人不是闰年，反之亦然。闰年也将导致1月初生日有时在12月底出现一天。如果有人想添加闰年校正，请随意：）

  SELECT birthdate 
 FROM Anniversaries 
在0到10之间的0至10 
或dayofyear（birthdate）+ 365  -  dayofyear（curdate（））的日期（出生日期） -  dayofyear（curdate（）） 
  

I have a table with anniversary dates. I want a query that returns me rows of anniversaries coming up in the next 10 days. For instance:

birthdate
---------
1965-10-10
1982-05-25


SELECT birthdate FROM Anniversaries WHERE mystical_magical_mumbo_jumbo <= 10

+------------+
| birthdate  |
+------------+
| 1982-05-25 |
+------------+
1 row in set (0.01 sec)

I'd like to keep the query in the form x <= 10, because I'll use that number 10 in other parts of the query, and if I set it to a variable, I can change it once everywhere by changing the variable, and not have to re-write the query.

解决方案

As others have stated, you need to ignore the year in your comparison. The DAYOFYEAR() function is one way to do that.

Here's a quick solution off the top of my head. It will return all birthdays in the next 10 days, even if it's late December and the birthday is next year.

It DOES NOT handle leap years properly, so it will be off by 1 day for early March birthdays if this year is a leap year and the person was not born in a leap year or vice-versa. Leap years will also cause early January birthdays to show up one day off in late December sometimes. If anyone wants to add the leap year correction, feel free :)

SELECT birthdate 
FROM Anniversaries 
WHERE dayofyear(birthdate) - dayofyear(curdate()) between 0 and 10 
or dayofyear(birthdate) + 365 - dayofyear(curdate()) between 0 and 10;

这篇关于发现在MySql中n天内是否有周年纪念日的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！