leap年的MySQL Dayofyear [英] Mysql Dayofyear in leap year

问题描述

在以下查询中，不考虑the年.

In the following query the leap year is not taken into account.

 SELECT      e.id,
             e.title,
             e.birthdate
 FROM        employers e
 WHERE       DAYOFYEAR(curdate()) <= DAYOFYEAR(e.birthdate)
 AND         DAYOFYEAR(curdate()) +14 >= DAYOFYEAR(e.birthdate)

因此，在此查询中，in年出生的某人的出生日期在非leap年中具有不同的日期.

So in this query the birthdate of someone who is born in a leap year has got a different dayofyear in a non leap year.

如何调整查询以确保它在a年也能正常工作?

How can i adjust the query to make sure it also works in a leap year?

我拥有的mysql版本是:5.0.67

The mysql version i have is: 5.0.67

推荐答案

在NOW()是非-年2011的情况下，问题出在2月29日之后的a年出生的人会有额外的一天，因为您正在针对出生年份使用DAYOFYEAR.

Where NOW() is a non-leap year 2011, the problem arises from anybody born on a leap year after February 29 will have an extra day because you are using DAYOFYEAR against the birth year.

DAYOFYEAR('2004-04-01') // DAYOFYEAR(e.birthdate) Returns 92
DAYOFYEAR('2011-04-01') // DAYOFYEAR(NOW()) Returns 91

在执行DAYOFYEAR的地方，您需要的是当前年份的出生日期，而不是出生年份.

Where you do DAYOFYEAR, you need the birthdate from the current year, not the year of birth.

所以，而不是:

DAYOFYEAR(e.birthdate)

您可以像这样将其转换为今年:

You can convert it to this year like this:

DAYOFYEAR(DATE_ADD(e.birthdate, INTERVAL (YEAR(NOW()) - YEAR(e.birthdate)) YEAR))

将生日转换为:

'2004-04-01'

收件人:

'2011-04-01'

因此，这是修改后的查询:

So, here's the modified query:

SELECT      e.id,
             e.title,
             e.birthdate
 FROM        employers e
 WHERE       DAYOFYEAR(curdate()) <= DAYOFYEAR(DATE_ADD(e.birthdate, INTERVAL (YEAR(NOW()) - YEAR(e.birthday)) YEAR))
 AND         DAYOFYEAR(curdate()) +14 >= DAYOFYEAR(DATE_ADD(e.birthdate, INTERVAL (YEAR(NOW()) - YEAR(e.birthday)) YEAR))

2月29日出生的人将在非le年(3月1日)降为3月1日.

People born on February 29th will fall on March 1st on non-leap years, which is still day 60.

这篇关于leap年的MySQL Dayofyear的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！