Displaying the daily increase using data from the database

user_16615348 0
Viewed 74

I have a table in which I store the data I get using PHP from the API, the table looks like this:

ID count timestamp
6285 123 21.11 18:54
6284 122 21.11 18:53
6283 121 21.11 18:52
6282 120 21.11 18:51

What I want to achieve is something like: In the last 24 hours, the number has gone up by X

To retrieve data from DB I use:

 while($row = mysqli_fetch_array($result))
{
// generating HTML table etc
// echo-ign data with $row['timestamp']
}

My thought was to get the first write in 24 hours and the last write and get the difference, but I am not able to transfer this idea to PHP

mysql-8.0 window-functions mysql php
2 Answers

Modern MySQL versions since v.8.0 provides window functions such 'FIRST_VALUE':

SELECT DISTINCT
    DATE(`timestamp`) as day,
    FIRST_VALUE(`count`) OVER (PARTITION BY DATE(`timestamp`) ORDER BY `timestamp`) as start_day_count,
    FIRST_VALUE(`count`) OVER (PARTITION BY DATE(`timestamp`) ORDER BY `timestamp` DESC) as end_day_count
FROM t;

PHP solution can be next (using PDO module):

<?php
$query = "
    SELECT DISTINCT
        FIRST_VALUE(`count`) OVER (PARTITION BY DATE(`timestamp`) ORDER BY `timestamp`) as start_day_count,
        FIRST_VALUE(`count`) OVER (PARTITION BY DATE(`timestamp`) ORDER BY `timestamp` DESC) as end_day_count
    FROM t WHERE DATE(`timestamp`) = '2021-11-21';";

$stmt = $pdo->prepare($query);
$stmt->execute();
$row = $stmt->fetch(PDO::FETCH_ASSOC);

print_r($row);

Here you can test PHP with MySQL window functions

or using mysqli:

<?php
$query = "
    SELECT DISTINCT
        FIRST_VALUE(`count`) OVER (PARTITION BY DATE(`timestamp`) ORDER BY `timestamp`) as start_day_count,
        FIRST_VALUE(`count`) OVER (PARTITION BY DATE(`timestamp`) ORDER BY `timestamp` DESC) as end_day_count
    FROM t WHERE DATE(`timestamp`) = '2021-11-21';";

$result = mysqli_query($mysqli, $query);

$row = mysqli_fetch_array($result);

print_r($row);

PHP mysqli demo

user_33993560

Can we say "I want to have the difference between the max and the min count of last 24 hours" ? Is count always increasing ?

Propal :

SELECT MAX(count) - MIN(count) AS up_24h
FROM your_table
WHERE timestamp >= NOW() - INTERVAL (24 * 60) MINUTE
user_102426500
Related