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

2

Hi, I'm having one with a question here, I'm creating a code to get how many users logged into my site today, but I saved the last date it logged with Unix Time Stamp.

I'm just getting the users who logged in the last 24 hours, but what I want is to get the users who logged in on the day.

Follow 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.

Anyone can help me, I'd be grateful, thank you for the attention :)

    
asked by anonymous 07.01.2018 / 17:17

2 answers

3

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

For MySQL

Just do it right at 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 with PHP is to simply determine when the day begins in Unix Timestamp, and add 86399 seconds, and find values in this range, 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 solution given is for other days, changing time() for the desired day, but if you want only those of 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 just the count

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

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

Then just get the single value returned instead of rowCount() .

    
07.01.2018 / 20:11
0

You can get users logged in on the day (in YYYY-mm-dd format) by using the DateTime of php and the DATE_FORMAT function of MYSQL. You could do this:

//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 FROM_UNIXTIME function converts from < in> Unix timestamp to the format Datetime , which in turn is formatted only for Date .

    
07.01.2018 / 18:07