How do I get rows for last 24 hours by unixstamp

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

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.

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

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

You must convert the timestamp file to date for the comparison.

SELECT * FROM table WHERE (from_unixtime(unixdate) >= NOW() - INTERVAL 1 DAY)