Timestamp

sandy1028

Hi,

The values from the database is shown below.

When there is no data for the timestamp 14:00:01
How can I find out the timestamp 14:00:01 is not there in database and in the graph I have to mark as 0.

Please help me with this

2008-02-26 13:50:01| 13.0
2008-02-26 14:20:01 | 13.0 |
| 2008-02-26 14:40:01 | 13.0 |
| 2008-02-26 15:00:01 | 13.0 |
| 2008-02-26 15:20:02 | 13.0 |
| 2008-02-26 15:40:01 | 13.0 |

johanafm

First off, the 13:50 entry seems to be off in time compared to the other entries. All other entries are at on the hour, 20 past or 40 past (HH:00 +n20 minutes). Could it be your 13:50 entry is the one that was supposed to be in the 14:00 slot, and if so, why isn't it?
Disregarding that discrepancy:
loop over the result set in some way
keep track of the timestamp for the previous entry. See if the new one is increased by 20 minutes with same hour, or at 00 minutes for next hour. If not, mark value as missing in graph, set the variable keeping track of your last timestamp as if you had found it... check next entry.

sandy1028

johanafm wrote:
First off, the 13:50 entry seems to be off in time compared to the other entries. All other entries are at on the hour, 20 past or 40 past (HH:00 +n20 minutes). Could it be your 13:50 entry is the one that was supposed to be in the 14:00 slot, and if so, why isn't it?
Disregarding that discrepancy:
loop over the result set in some way
keep track of the timestamp for the previous entry. See if the new one is increased by 20 minutes with same hour, or at 00 minutes for next hour. If not, mark value as missing in graph, set the variable keeping track of your last timestamp as if you had found it... check next entry.

Yes it was the mistake when I altered the 13:40:01.
How to loop and check tht timestamp.

johanafm

Assuming you always have the same time format:
$time = "2008-02-26 14:40:01"
user substr to get hour and minute.
$hour = substr($time, 11, 2);
$min = substr($time, 14, 2);

How to loop will depend on how you retrieve and store the data from the database. Personally, I like working with non-associative arrays for this:
first make sure you have something in $result... and then

$last_hour = substr($result[0], 11, 2);
$last_min = substr($result[0], 14, 2);
// write entry to graph...

for ($i = 1; $i < count($result); $i++) {
   $hour = substr($result[$i], 11, 2);
   $min = substr($result[$i], 14, 2);


   if ($hour == $last_hour && $min == $last_min + 20 ||
      $hour = $last_hour + 1 && $min == 0 && $last_min == 40) {
            // entry found, proceed normally
   }
   else {
            // entry missinig, proceed as needed
   }

   $last_hour = $hour;
   $last_min = $min;
}