Compare timestamps for day

[Altec]

I need to get the count of how many people have signed my petition on the current day. I have this:

$time = date("d");
$getcount = "SELECT * FROM `signatures` WHERE `timestamp`='{$time}'";
$timequery = mysql_query($getcount) or die(mysql_error());
$signed_today = mysql_num_rows($timequery);

 

Which obviously will return 0 (zero). I need to get each timestamp and compare them for the day only. I have 600 and growing records so I don't want anything heavy. Can anyone help?

[Daniel0]

What type is your timestamp field?

[Altec]

INT(20)

[Daniel0]

Okay so I take it it's a UNIX timestamp. You can just check if the timestamp is within range of the possible UNIX timestamps for that particular day.

[chaking]

<?php
$right_now = time();
$back_24 = $right_now - (24 * 60 * 60);
$getcount = "SELECT * FROM `signatures` WHERE (`timestamp`> $back_24 AND `timestamp` < $right_now)";
?>

 

Grabs records in the last 24 hour range

[Daniel0]

That is not what he wanted though. He wanted entries on a particular day, not entries within a 24 hour interval. If you want the entries for e.g. May 23rd, but the time is 11:00 AM, then -24 hours up until now would be May 22nd 11:00 AM to May 23rd 11:00 AM. This means you, incorrectly, will get entries from the yesterday as well.

 

What you can do is something like this:

 

$timestamp = time();
$start = mktime(0, 0, 0, date('n', $timestamp), date('j', $timestamp), date('Y', $timestamp));
$end = mktime(23, 59, 59, date('n', $timestamp), date('j', $timestamp), date('Y', $timestamp));

$getcount = "SELECT * FROM `signatures` WHERE `timestamp` BETWEEN {$start} AND {$END}";