Calculating with timestamp and query extra row

[elmar]

Hi all,

 

I can't seem to figure out the right way to get this working.

 

At the moment I'm graphing the last 24 hours with the following code (part of it):

<?php
$sql = mysql_query("SELECT TIME_FORMAT(rec_time, '%H:%i'),rain_1h,rain_24h FROM weatherinfo where 
timestamp > (select date_format(timestamp (now(),'-24:00:00'),'%Y%m%d%H%i%s'))") or die(mysql_error());

while($row = mysql_fetch_array($sql))
{
$leg[] = $row[0];
$rain1h[] = $row[1];
$rain24h[] = $row[2];
}
?>

 

The graph looks like this:

 

In the database table I also have a timestamp field (eg: 20070212214505) and a field rain_total (an ever growing number).

 

What I try to make is a bar-graph which shows the amount of rain that falls in a 5 minute time period (this is also the update frequencu for the database).

 

So, when I have a row, I know the rain_total and have to substract the rain_total from 1 row earlier in the database.

 

In MySQL I can calculate the first 12 positions of the earlier timestamp:

select date_format(date_sub('20070212000007',interval 5 minute),'%Y%m%d%H%i')

but it won't work to get it done in php and get an array to graph.

 

 

[elmar]

The code how this works is:

$sql = mysql_query("SELECT TIME_FORMAT(rec_time, '%H:%i'),rain_1h,rain_24h,rain_total FROM weatherinfo where timestamp > (select date_format(timestamp (now(),'-24:00:00'),'%Y%m%d%H%i%s'))") or die(mysql_error());

$count=0;

while($row = mysql_fetch_array($sql))
{

$leg[] = $row[0];
$rain1h[] = $row[1];
$rain24h[] = $row[2];
$raintot[] = $row[3];

if ($count >= 1)
{
$prev=$count-1;
$rain5m[] = $row[3] - $raintot[$prev];
}
$count++;

 

The graph now looks like this:

 

@Jim: I have problems sending personal messages