Counting rows where a timestamp is less than 24 ho

问题:

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(); 

回答1:

Use:

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

回答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()

回答3:

$message="select count(1)
from privatemessage
where datesent>=date_sub(now(), 24 hours)
and recipient='$u'"

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-add

回答4:

I see this is a old one though for anybody that gets here trough an online search later on, I would like to clarify that the question is about a timestamp, not datetime. I needed a count based on timestamp. Easy fix is to convert the datetime to unix_timestamp. Tested on MySQL 5.7

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