Page 1 of 1

Problems with dates in MySQL statement

Posted: Mon Apr 28, 2003 12:19 pm
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?

Re: Problems with dates in MySQL statement

Posted: Mon Apr 28, 2003 12:44 pm
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

Posted: Mon Apr 28, 2003 2:47 pm
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

Posted: Mon Apr 28, 2003 3:39 pm
by Swede78
Thanks, Mac.

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