How to order day data in SQL with Unix Time Stamp (PHP)

Question

Hello I’m with a doubt here, I’m creating a code to get how many users have logged on to my site today, but I saved the last date he logged on with Unix Time Stamp.

I’m only able to get users who have logged in in the last 24 hours, however, what I want, is to get users who have logged in on the day.

Follows my code:

$time_24h = time()-86400;

$users_today = $sql->query("SELECT * FROM {$table_prefix}_Users WHERE last_used > {$time_24h}")->rowCount();

I’m using the PDO connection.

who can help me, would be very grateful, thank you for the attention :)

Answer 1

The ideal would solve in the SQL layer, which depends on the engine used.

For Mysql

Just do it right on select:

                       .--- converte de Unix para Timestamp, compativel com MySQL
                       |
SELECT ... WHERE DATE(FROM_UNIXTIME(last_used)) = CURRENT_DATE;
                  |                                |
                  '--- extrai a data, sem horas    '--- compara com data de hoje

Solving with PHP

One way to do it with PHP is simply to determine when the day begins on Unix Timestamp, and add 86399 seconds, and locate values on this track, thus:

$hoje = time();                            // Pega o timestamp do servidor já em segundos
$iniciododia = ( $hoje % 86400 ) * 86400;  // "arredonda" para 0h00 do dia
$fimdodia = $iniciododia + 86399;           // e obtem o 23h59m59 do dia

Applying to your query:

"SELECT * FROM {$table_prefix}_Users WHERE last_used BETWEEN $iniciododia AND $fimdodia"
                                                     |
           retorna dados entre 0h00m00 e 23h59m59 ---'

The given solution is for other days, changing the time() for the desired day, but if you only want the current day, you can simplify even more:

$hoje = time();                            // Pega o timestamp do servidor já em segundos
$iniciododia = ( $hoje % 86400 ) * 86400;  // "arredonda" para 0h00 do dia
// ... desnecessário calcular o fim do dia para data atual ...
"SELECT * FROM {$table_prefix}_Users WHERE last_used >= $iniciododia"

If it’s only the count

Remember that it is unnecessary to bring all the data if you just want to know the count. The way your query original, you are bringing all the DB data to the desired date unnecessarily. Consider doing this:

"SELECT COUNT(*) FROM {$table_prefix}_Users WHERE last_used >= $iniciododia"

then just take the only value returned instead of the rowCount().

Answer 2

You can get users logged in on the day (in YYYY-mm-dd format) using the class DateTime php and the function DATE_FORMAT mysql. You could do so:

//dataAtual  terá o formato YYYY-mm-dd, ou 0000-00-00, ano com quatro
//digitos, mes e dia com dois
$dataAtual = (new DateTime())->format('Y-m-d');

//aqui você formata last_used no formato YYYY-mm-dd
$users_today = $sql->query("SELECT * FROM {$table_prefix}_Users WHERE
DATE_FORMAT(FROM_UNIXTIME(last_used), '%Y-%m-%d') = {$dataAtual}")->rowCount();

The function FROM_UNIXTIME converts from format Unix timestamp for the format Datetime, which in turn is formatted only for Date.