Counting rows where a timestamp is less than 24 hours old

Question

For the query below, how could I count the number of rows where datesent is less than 24 hours old? (The field datesent is a timestamp).

Thanks in advance,

John

  $message = "SELECT datesent, recipient                FROM privatemessage                WHERE recipient = '$u'";     $messager = mysql_query($message);  $messagearray = array();  

Answer 1

Use:

SELECT COUNT(*) AS cnt   FROM PRIVATEMESSAGE pm  WHERE pm.datesent >= DATE_SUB(NOW(), INTERVAL 1 DAY) 

Answer 2

You can use the DATE_SUB function. Subtract one day from the current date in the where clause.

Something like

DATE_SUB(NOW(), INTERVAL 1 DAY) 

EDIT: changed CURTIME() to NOW()