Displaying the daily increase using data from the database-CodePudding

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

CodePudding user response：

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

CodePudding user response：

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