Finding the date 30 days ago...?

PHP
1

I have some news articles in my mysql db. I want to display all the articles posted up in the past month. I know how to get todays date - date(“d m Y”), but how can I find out the date a month ago? Once I have that, I guess the sql will be something like “SELECT * FROM news WHERE newsDate >= {whatever 30 days ago was}”
The field is a TimeStamp with default value of current date. Its stored in this format - 2008-11-10 12:10:02
Thanks

2

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html


select *
from news
where date(newsDate) >= date(now()-interval 30 day)

or if you want to give literal dates


select *
from news
where date(newsDate) between '2008-10-10' and '2008-11-10'

The db convert automatically your text input of dates to datetime, so no need to handle this yourself.

3

And if you want to get the 30 days ago date in PHP:


echo date('Y-m-d', strtotime('-30 days'));
4

Thanks. That looks easy.

5

actually, it isn’t

internal storage formats are completely unrecognizable (if you were ever able to see them, which you can only do by reading the actual physical storage files yourself)

also, “past 30 days” is not the same as “past month”

it is the difference between 2008-10-13 to 2008-11-13 and 2008-10-01 to 2008-10-31

tripy, don’t forget that if you do this –

date(newsDate) >= date(now()-interval 30 day)

then by putting a function on the column, you’re forcing the optimizer into a table scan because it can’t use an index on the column

6

tripy, don’t forget that if you do this –
Code:

date(newsDate) >= date(now()-interval 30 day)

then by putting a function on the column, you’re forcing the optimizer into a table scan because it can’t use an index on the column

Now that you say it, it struck me.
cause now()-interval is not… what’s the term now… predictable ? Deterministic ?

7

actually, date(now()-interval 30 day) will be evaluated only once, before the query executes

the problem is with date(newsDate) – the function applied to the column is what causes the optimizer to abandon using the index on that column