gerkintrigg Posted July 15, 2013 Share Posted July 15, 2013 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 Quote Link to comment https://forums.phpfreaks.com/topic/280181-annual-selector/ Share on other sites More sharing options...
Barand Posted July 16, 2013 Share Posted July 16, 2013 (edited) 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 Quote Link to comment https://forums.phpfreaks.com/topic/280181-annual-selector/#findComment-1440850 Share on other sites More sharing options...
ragax Posted July 26, 2013 Share Posted July 26, 2013 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. Quote Link to comment https://forums.phpfreaks.com/topic/280181-annual-selector/#findComment-1442337 Share on other sites More sharing options...
ragax Posted July 27, 2013 Share Posted July 27, 2013 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. Quote Link to comment https://forums.phpfreaks.com/topic/280181-annual-selector/#findComment-1442346 Share on other sites More sharing options...
ragax Posted July 27, 2013 Share Posted July 27, 2013 (edited) 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 Quote Link to comment https://forums.phpfreaks.com/topic/280181-annual-selector/#findComment-1442451 Share on other sites More sharing options...
ragax Posted July 28, 2013 Share Posted July 28, 2013 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 ) Quote Link to comment https://forums.phpfreaks.com/topic/280181-annual-selector/#findComment-1442461 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.