I have records in MySQL base and each record has date field, it's of 'datetime' format. I want to select a record one year old and I'm currently having this:


A strange things happen here. It was working for two days and for I get the same article for three days now. Now is 11th and I have the article from January 8th for three days. I looked at MySQL manual and I found something like this:

but that returns nothing. That's why I came up with such a complicated query. If anybody has any idea about fetching a record one year old I'd be very grateful.

MySQL date stuff always struck me as creaky. It does silly things like using server local time (14? hours away from here).

I would do all the date arithmetic in php (see time() date() gmdate() etc) and reduce the amount you have to struggle with.

Or consult an SQL guru.

That's true, I get used to it.


I asked some "gurus", I'll try with php, thank you.

I forgot mktime() which is what you do the arithmetic with. Example: here's a fragment that builds arrays mapping datecodes (my own format) to appropriate names for a selectbox that offers the next AVAILDAYS (future) dates as options:


$nowyyyy is the current year etc. (Note you should get the current date from one function call, just in case midnight comes along...) So for one year ago you'll need something like:

$stamp = mktime(12,0,0, $nowmm, $nowdd, $nowyyyy-1);

mktime() does all the normalisation, so things like

$stamp = mktime(12,0,0, $nowmm+1, $nowdd, $nowyyyy-1);

...work, even if it's January 30th (so one month later, one year earlier is always 11 months ago, near enough).

It seems that the most simple solutions are the hardest to find Sad I ended up with very simple and not elegant solution, but it works:

SELECT * FROM `articles` WHERE (YEAR(date)=YEAR(CURRENT_DATE())-1) AND (DAYOFMONTH(date)=DAYOFMONTH(CURRENT_DATE())) AND (MONTH(date)=MONTH(CURRENT_DATE())) LIMIT 1

Not the best solution in the world, not the most elegant, but it works. And I see the application for Chandler's example above

Does it work on February 29th? I think you will get an empty result. Which might be OK, depending on how you are using this...

Since in the previous year we didn't publish articles every day, I have something like this:
IF there is an article
THEN show it
ELSE do nothing

So, I really don't care for February 29th, but thank you for your concern