0

I'm trying to select records from a table created in the last 24 hours using the function below. I don't understand why it is returning all records rather than just last 24 hours worth (created > UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 24 HOUR)).

Any advice appreciated.

function getActive()
{
global $dbh;

$statement  =   $dbh->prepare("SELECT * FROM jobs WHERE created > UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 24 HOUR))");

$statement->setFetchMode(PDO::FETCH_CLASS|PDO::FETCH_PROPS_LATE, 'Jobs');

$statement->execute();      
$result     =   $statement->fetchAll();


return ($result)?$result:array();
}
Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
Barry Hamilton
  • 943
  • 3
  • 15
  • 35
  • what's the type of `created`? Unless it's an int and storing an actual unix timestamp (seconds since Jan 1/1970), your comparison will fail because it'd be an apples/oranges situation. – Marc B Nov 14 '13 at 20:13
  • What data type is `created`? It should be `DATETIME` as native data types are often better than forcing external conventions in your database. – tadman Nov 14 '13 at 20:13
  • the data type is 'timestamp' – Barry Hamilton Nov 14 '13 at 20:27
  • Marc you are absolutely right I didn't even notice '> UNIX_TIMESTAMP' fixed the fixed the type a in the query and all is well again. – Barry Hamilton Nov 14 '13 at 20:38

1 Answers1

0

first check your created column data type if it's correct then try this

SELECT * FROM jobs WHERE created < date_add(NOW(), INTERVAL -24 HOUR)
Airy
  • 5,484
  • 7
  • 53
  • 78