Page 1 of 1

Getting today's date from MySQL

Posted: Wed Apr 20, 2005 3:43 am
by lazersam
Hi

I have stored timestamps in a database table in a feild called hit_date and would like to extract only those rows for today!

Code: Select all

$the_date = time();
		//calculate today
		$today = date("Ymd", $the_date);
			
		#Get hits today
		$sql = "select * from hits where aff_id = \"Admin\" and hit_date >= \"$today\"  ";
		$qry = mysql_query($sql, $conn) or die ("DID NOT hits "  .mysql_error());
		$num_hits_today = mysql_num_rows($qry);
This returns everything probably because $today = 20050420 which is less than the timestamps.

Do you know what the mysql line should read?

Larry.

Posted: Wed Apr 20, 2005 6:34 am
by John Cartwright

Code: Select all

#Get hits today        
$qry = mysql_query("SELECT * FROM `hits` WHERE `aff_id` = 'Admin' and `hit_date` >= '".time()."'", $conn) or die ("DID NOT hits "  .mysql_error());        
$num_hits_today = mysql_num_rows($qry);
date converts your unix timestamp into readable dates, while your stored date in mysql is still a unix timestamp.

Posted: Wed Apr 20, 2005 7:05 am
by feyd
I prefer

Code: Select all

SELECT * FROM `hits` WHERE `aff_id` = 'Admin' AND DATE_FORMAT(`hit_date`, '%Y-%m-%d') = CURDATE()
or

Code: Select all

SELECT * FROM `hits` WHERE `aff_id` = 'Admin' AND DATE_FORMAT(FROM_UNIXTIME(`hit_date`), '%Y-%m-%d') = CURDATE()