Annual Selector

[gerkintrigg]

Hello,

 

I'm trying to write a script that can get an anniversary record from a database. I can do everything if I know the year, but I want to get all years...

 

So the code: 

SELECT  * FROM `anni_reminder` WHERE `date` > ADDDATE(NOW(), INTERVAL -1 WEEK)

Works fine if the year is this year, but I want to go back 100 years and see if the anniversary was set for this week in 1913 for example.

 

I can't think of an easy way to loop unless I execute 100 SQL queries, but that doesn't seem a very slick way to do it.

 

Any suggestions?

 

Neil

[Barand]

One way (anniversaries in the next seven days)

SELECT thedate FROM dates
WHERE DATE_FORMAT(thedate,'%m%d') 
    BETWEEN DATE_FORMAT(CURDATE(),'%m%d')
    AND DATE_FORMAT(CURDATE() + INTERVAL 7 DAY,'%m%d')

edit : caution -  it won't work if 7-day range spans year end

Edited July 16, 2013 by Barand

[ragax]

Here are some thoughts.
Assuming your date column is indexed (let's call it mydate for clarity), then we want the WHERE condition to apply to mydate without any transformation, to allow the optimizer to use the index.

So we want a WHERE for mydate that falls between pastcurdate and pastcurdate + 7, where pastcurdate is the current date transformed to an equivalent date in the past, close to mydate.

How do we transform CURDATE() to pastcurdate? We can't just date_format it with the year of mydate, first for the reason Barand put forth (year-end cases), second because that may result in an invalid date when CURDATE() is Feb 29 and the year of mydate is not a leap year.

So my feeling is the transformation from CURDATE to pastcurdate will involve a couple of CASE... WHEN clauses.

That's as far as I got (after first trying, and failing, to think of a suitable modulo).

If that direction seems promising I'm sure you can finish it up. But someone may come up with a better idea.

Wishing you a fun weekend.

[ragax]

Okay, I think this will work:

SELECT thedate,
@d := DATE_FORMAT( CURDATE(),
    CONCAT(
        CASE
        WHEN DAYOFYEAR(CURDATE()) >358 THEN YEAR(thedate) - 1
        ELSE YEAR(thedate)
        END
        ,
        '-%m-',
        CASE WHEN DAY(CURDATE())=29 AND MONTH(CURDATE()) = 2
        THEN 28
        ELSE DAY(CURDATE())
        END
    )
)

 AS pastcurdate ,
 @d + INTERVAL 7 DAY AS laterdate
 
FROM
date
GROUP BY thedate
HAVING thedate BETWEEN pastcurdate AND laterdate

Let me know if it does.

[ragax]

Hi again gerkintrigg,

 

After browsing through the big bag of MySQL time and date functions to refresh my memory, I came up with something neater.

SELECT `date`
FROM
(
SELECT `date`,
@d:=TIMESTAMPDIFF(YEAR,CURDATE(),`date`),
@p:=TIMESTAMPADD(YEAR,@d-1,CURDATE() ) AS pastanniv
FROM anni_reminder
) t
WHERE `date` BETWEEN pastanniv AND pastanniv +INTERVAL 7 DAY

If you need other fields in the outer query, remember to add them to the inner query as well.

Please let me know if this does the trick.

 

 

Edited July 27, 2013 by ragax

[ragax]

The same, but more compact:

SELECT `date`
FROM anni_reminder
WHERE `date` BETWEEN 
(@p := TIMESTAMPADD(YEAR,TIMESTAMPDIFF(YEAR,CURDATE(),`date`)-1,CURDATE()) )
AND ( @p + INTERVAL 7 DAY )