Help with one month ago

jbachris

I'm trying to get a news item from my database that happened one month ago.

I have:

SELECT COUNT(np_id) as news_headline, news_date FROM news ORDER BY news_date

What do I need to do in a WHERE clause to get only news items from that date one month ago?

NogDog

I think this would work:

WHERE news_date > DATE_SUB(CURDATE(), INTERVAL 1 MONTH)

jbachris

It works in displaying all items in the past month. What I am trying to do is when someone goes to the site, it ONLY displays news items from that same date a month ago.

Since today is August 2nd, it would only display news items from July 2nd.

TobyRT

I suspect it might work if you replaced the '>' with a '=', as the '=' removes the range.

jbachris

I tried the '=' but again nothing.

I think it has to do with it not equalling exactly. My timestamp is in YYYY-MM-DD hh:mm:ss

So, if I add a news story at 05:30:15 on July 2nd, it would only equal DATE_SUB(CURDATE(), INTERVAL 1 MONTH) at 05:30:15 on August 2nd.

How can I get it to only to read the month, day, and year?

cahva

Use:
WHERE DATE(news_date) = DATE_SUB(CURDATE(), INTERVAL 1 MONTH)