Total of all values in a day (24h)

Question

I'd like to add all values in a database from the last 24 hours. Each value has its TimeStamp in the database.

I tried to do that with a for-loop, which adds 86400 secs (24h) every time, picks all values from one day and after that it adds all values.

Heres my code:

> `$datestart = 153839251200; //start date

for($uts = $uts; $uts > $datestart; $datestart + 86400){ 

if (($uts <= ($datestart + 86400)) && ($uts > $datestart)){

$uts = $datestart + 86400;

$valueFinal = $valueFinal + $value;   

 }
}

if($Zeitalt != $uts){

  $Zeitalt=date('l, F j y H:i:s',$uts);
  $uts *= 1000; // convert from Unix timestamp to JavaScript time 
  $data[] = array((float)$uts,(float) $valueFinal);


 }`

I hope this explanation is enough, I'm not English speaking as much, otherwise just ask for more information.

Regards DR.Alfred

What is the question ? You have given us PHP code; with no details about your table structure and some relevant sample data. — Madhur Bhaiya, Nov 14 '18 at 14:12
https://stackoverflow.com/a/1888593/522479 could be interesting. — Cobra_Fast, Nov 14 '18 at 14:27

score 0 · Answer 1 · answered Nov 14 '18 at 17:07

0

You've tagged this as SQL so I'm assuming an answer in SQL is what you're looking for.

Firstly, I'm not sure why you're going to the effort of using a loop to get the total when SQL has the SUM function.

I'll give you this in T-SQL as I'm not familiar with MySQL but it shouldn't be too difficult to change it to MySQL:

SELECT 
    SUM(YourValue) 
FROM 
    YourTable 
WHERE 
    YourTimeStamp > getdate() - 1

I think the MySQL equivalent of GETDATE() is NOW().

answered Nov 14 '18 at 17:07

codemac

31
4

I tried it like this: $result = mysql_query("SELECT SUM($total) * FROM `Messwerte` WHERE $uts >= NOW() - INTERNAL 1 DAY"); //$uts: TimeStamp – DR.Alfred Nov 15 '18 at 06:51
So what happened when you ran this? I'm not an expert on MySQL but I wouldn't think you would need the * in the select. Also, you should be using INTERVAL, not INTERNAL. – codemac Nov 15 '18 at 16:59

Total of all values in a day (24h)

1 Answers1