SQL Query to show number of stories created in last 24 hours?

Question

I'm trying to create a custom query that will show the number of stories that have been posted in the last 24 hours on a Drupal 6 site.

Stories are stored in the "node" table. each record has a "created" row that records the UNIX timestamp when the story was posted.

Here's the query I'm trying so far:

$sq = 'SELECT COUNT(*) cnt '
    . 'FROM {node} c WHERE created >= dateadd(hour,-24,getdate())'; 

This doesn't appear to be working though. What am I doing wrong?

EDIT: Here's the overall code I'm trying to use right now:

$sq = 'SELECT COUNT(*) AS cnt FROM {NODE} n WHERE FROM_UNIXTIME(n.created) >= DATE_SUB(NOW(), INTERVAL 1 DAY)';                                                                                        

$q = db_query($sq);

while ($o = db_fetch_object($q)) {

            print_r($o);

}

That print_r isn't returning anything. Where's my error?

Answer 1

For MySQL, use:

SELECT COUNT(*) AS cnt
  FROM NODE n
 WHERE FROM_UNIXTIME(n.created) >= DATE_SUB(NOW(), INTERVAL 1 DAY)

Mind that NOW() includes the time when the statement is run. If you want to count records, starting from midnight of the previous day, use:

SELECT COUNT(*) AS cnt
  FROM NODE n
 WHERE FROM_UNIXTIME(n.created) >= DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY)

Reference:

• FROM_UNIXTIME
• DATE_SUB

Answer 2

Since you are doing this in PHP, you can just use $_SERVER['REQUEST_TIME']. My guess is that it will be faster than doing date manipulations with SQL:

$count = db_result(db_query("SELECT COUNT(nid) FROM {node}
          WHERE created >= %d;", $_SERVER['REQUEST_TIME'] - 86400));

Alternative you could use time to get the current timestamp, but that will be a tiny bit slower than using the $_SERVER['REQUEST_TIME'] variable.