Problems with dates in MySQL statement

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Swede78
Forum Contributor
Posts: 198
Joined: Wed Mar 12, 2003 12:52 pm
Location: IL

Problems with dates in MySQL statement

Post by Swede78 »

I'm trying to get a recordset of all records with a release date newer than 60 days. I first create a date variable of 60 days ago. Then use it in my MySQL query statement shown below. However, when I display the results, I get ALL the records, regardless of date. My dates are stored in DATE formatted data in MySQL.

$TwoMonthsAgo = date("Y-m-d", mktime(0,0,0, date(m), date(d)-60,date(Y)));

SELECT *
FROM music
WHERE ReleaseDate > $TwoMonthsAgo

I've been reading up on much of the date functions in PHP, but I'm not sure what the best way to go about this is. Am I at least on the right track?
Swede78
Forum Contributor
Posts: 198
Joined: Wed Mar 12, 2003 12:52 pm
Location: IL

Re: Problems with dates in MySQL statement

Post by Swede78 »

Swede78 wrote: $TwoMonthsAgo = date("Y-m-d", mktime(0,0,0, date(m), date(d)-60,date(Y)));

SELECT *
FROM music
WHERE ReleaseDate > $TwoMonthsAgo
I figured it out. This seems to work. If anyone knows of a better way or reason this is not a good method, please post.

$TwoMonthsAgo = mktime(0,0,0, date(m), date(d)-60,date(Y));

SELECT *
FROM music
WHERE UNIX_TIMESTAMP(ReleaseDate) > $TwoMonthsAgo
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

You could do the comparison using SQL:

Code: Select all

SELECT * FROM music WHERE ReleaseDate >= DATE_SUB(CURDATE(), INTERVAL 2 MONTH)
http://www.mysql.com/doc/en/Date_and_ti ... tions.html

Mac
Swede78
Forum Contributor
Posts: 198
Joined: Wed Mar 12, 2003 12:52 pm
Location: IL

Post by Swede78 »

Thanks, Mac.

That works just as well, and is a little cleaner.
Post Reply