How do I get rows for last 24 hours by unixstamp

Question

I have this;

$long = "86400";
$query = "SELECT * FROM users WHERE unixdate = UNIX_TIMESTAMP()-$long 
          ORDER BY unixdate DESC";

But it doesn't work. I would like to show all new users within 24 hours

Answer 1

Use > instead of =. At the moment, you are querying for entries created at a certain second which will hardly ever match.

Answer 2

You're looking for new users within the last 24h, not exactly 24h. So you have to use the > (greater than) operator instead of = (equals).

$long = "86400";
$query = "SELECT * FROM users WHERE unixdate > UNIX_TIMESTAMP()-$long ORDER BY unixdate DESC";

By the way, PHP has a function equivalent to MySQL UNIX_TIMESTAMP() function: time();

Answer 3

You can do that query completely in MySQL with

SELECT col1, col2, otherCols
    FROM yourTable 
    WHERE timestamp_col > (NOW() - INTERVAL 24 HOUR)

The expression (NOW() - INTERVAL 24 HOUR) returns the date 24 hours ago. MySql is smart enough to handle comparisons between Time related column types.

If timestamp_col is not a time related type, but something like a varchar or int column you have to use FROM_UNIXTIME on the column or adjust the above query to read

SELECT col1, col2, otherCols
    FROM yourTable 
    WHERE timestamp_col > UNIX_TIMESTAMP( NOW() - INTERVAL 24 HOUR )

See DATE_SUB and DATE_ADD in the MySql Manual.