开发者

finding if an anniversary is coming up in n days in MySql

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_mum开发者_运维知识库bo_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;


Try this

SELECT birthdate FROM Anniversaries 
WHERE DATEDIFF(CURTIME(),birthdate) >= 0 
AND DATEDIFF(CURTIME(),birthdate) <= 10 

Check this for reference MySQL DATEDIFF

DATEDIFF(expr1,expr2)

DATEDIFF() returns expr1 – expr2 expressed as a value in days from one date to the other. expr1 and expr2 are date or date-and-time expressions. Only the date parts of the values are used in the calculation.

mysql> SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30'); -> 1

mysql> SELECT DATEDIFF('2010-11-30 23:59:59','2010-12-31'); -> -31


MAKEDATE() creates a date from a year and the day of year, so I can use it to get old years out of the picture:

SELECT 
    anniversary
    ,  MAKEDATE( YEAR(NOW()), DAYOFYEAR(anniversary) ) AS thisyear 
FROM Anniversaries;

+-------------+-------------+
| anniversary | thisyear    | 
+-------------+-------------+
| 1978-07-29  | 2010-07-29  |
| 1959-04-17  | 2010-04-17  |
+-------------+-------------+

Then I can use DATEDIFF() calculate the days until ( or from ) then:

SELECT 
    anniversary
    , MAKEDATE( YEAR(NOW()), DAYOFYEAR(anniversary) ) AS thisyear
    , DATEDIFF( MAKEDATE(YEAR(NOW()),DAYOFYEAR(anniversary)), NOW()) as days 
FROM Anniversaries;

+-------------+-------------+------+
| anniversary | thisyear    | days | 
+-------------+-------------+------+
| 1978-07-29  | 2010-07-29  |   70 |
| 1959-04-17  | 2010-04-17  |  -33 |
+-------------+-------------+------+

Assuming NOW() is 5/20.


Edit so the above doesn't work over year rollover. One solution is to add another calculation, where the anniversary is the next year. Here I've hard-coded the date '2010-12-31', and used an OR in a HAVING clause to filter by days that match either this year or next:

SELECT birth_date
, MAKEDATE(YEAR('2010-12-31'),DAYOFYEAR(birth_date)) as anniversary
,  DATEDIFF( MAKEDATE(YEAR('2010-12-31'),DAYOFYEAR(birth_date)), '2010-12-31') as days
, MAKEDATE(YEAR(NOW())+ 1,DAYOFYEAR(birth_date)) as next_anniversary
, DATEDIFF( MAKEDATE(YEAR(NOW())+ 1,DAYOFYEAR(birth_date)), '2010-12-31') as next_days 
FROM Anniversaries 
HAVING ( ( days <= 25 AND days > 0 ) OR next_days <= 25 );

+------------+-------------+------+------------------+-----------+
| birth_date | anniversary | days | next_anniversary | next_days |
+------------+-------------+------+------------------+-----------+
| 2010-01-23 | 2010-01-23  | -342 | 2011-01-23       |        23 |
| 1975-01-11 | 2010-01-11  | -354 | 2011-01-11       |        11 |
+------------+-------------+------+------------------+-----------+
2 rows in set (0.00 sec)


What we want to achieve is a list of anniversaries occurring in a recent date range. To achieve this and to accommodate rollover we need to generate a list of all anniversaries that cover the date range. The solution that I have used, generates anniversary records for last year, this year and next year and outputs only those records that fall within the given date range. The union query looks like this:

SELECT Date_Add(birth_date,INTERVAL (Year(now())-Year(birth_date)-1) YEAR) as dt,  
FROM Anniversaries 
WHERE Date_Add(birth_date,INTERVAL (Year(now())-Year(Date)-1) YEAR) 
BETWEEN Date_sub(now(), INTERVAL 7 Day) AND Date_add(now(), INTERVAL 21 Day) 
UNION
SELECT Date_Add(birth_date,INTERVAL (Year(now())-Year(birth_date)) YEAR) as dt,
FROM Anniversaries 
WHERE Date_Add(birth_date,INTERVAL (Year(now())-Year(Date)) YEAR) 
BETWEEN Date_sub(now(), INTERVAL 7 Day) AND Date_add(now(), INTERVAL 21 Day) 
UNION
SELECT Date_Add(birth_date,INTERVAL (Year(now())-Year(birth_date)+1) YEAR) as dt,
FROM Anniversaries 
WHERE Date_Add(birth_date,INTERVAL (Year(now())-Year(Date)+1) YEAR) 
BETWEEN Date_sub(now(), INTERVAL 7 Day) AND Date_add(now(), INTERVAL 21 Day) 
ORDER BY dt

The first SELECT statement increments all the dates to last year and selects any items that fall into the date range provided The next SELECT statement increments all the dates to the current year and selects any items that fall into the date range provided The last SELECT statement increments all the dates to next year and selects any items that fall into the date range provided Finally it is all ordered by date.

There you have it. This will reliably select anniversaries in the given period. If a longer period is required, it may be necessary to generate additional SELECT statements and join them with UNION. I have modified my code to match the example above but have not tested it since modification.


You could actually use an index if you stored the birthday separate from the birthdate. The birthday column would have the year and month, but would all have the same year (2000, for example).

Then you'd do your query like this:

SELECT birthdate
FROM Anniversaries
WHERE birthday >= DATE_ADD(MAKEDATE(2000, DAYOFYEAR(CURDATE())), INTERVAL 10 DAYS)

While the expression isn't exactly as you desired, the number 10 is still on the right side of the expression. Applying the function to the right side of the expression and keeping just the column on the left side of the expression allows MySQL to use an index on the birthday column (assuming you have an index).


     SELECT firstName, lastName, DOB, if( (
     datediff( date( concat( year( current_date ) , '-', month( DOB ) , '-', day( DOB ) ) ) , current_date ) ) >=0, (
    datediff( date( concat( year( current_date ) , '-', month( DOB ) , '-', day( DOB ) ) ) , current_date )), 
    datediff( date( concat( year( current_date ) +1, '-', month( DOB ) , '-', day( DOB ) ) ) , current_date )
)DAYSTOBDAY
    FROM birthinfo
    WHERE (
    if( (
    datediff( date( concat( year( current_date ) , '-', month( DOB ) , '-', day( DOB ) ) ) , current_date ) ) >=0, (
    datediff( date( concat( year( current_date ) , '-', month( DOB ) , '-', day( DOB ) ) ) , current_date )),
    datediff( date( concat( year( current_date ) +1, '-', month( DOB ) , '-', day( DOB ) ) ) , current_date )
    )
    )
    BETWEEN 0
    AND 31

   firstName    lastName    DOB             DAYSTOBDAY
   Michelle     Smith       1979-04-16          17
   Kirsten      Dunst       1982-04-30          31

It is so far from being a 'nice' answer it is not funny, but off the top of my head that works. to achieve what you want. I created a table with firstname, lastname and DOB, you can just substitute it all around of course.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜