CODEWITHSUNDEEP
phpdatetime
muttley91
muttley91

Reputation: 12674

Average Time in Seconds

I'm using the following code to calculate the average time between a start time and an end time for a number of events (from a database):

function getATBData($siteID, $fromDate, $toDate)
{
    global $pdo;

    $ATBarray = array();
    $maxATB;
    $minATB;
    $avgATB;
    $totalATB=new DateTime("@0");
    $totalEvents=0;
    $timetable;

    $query = "SELECT id, siteID, start_time, end_time FROM atb_log WHERE siteID=:siteID AND (start_time BETWEEN :fromDate AND :toDate) AND (end_time BETWEEN :fromDate AND :toDate)";
    $stmt = $pdo->prepare($query);
    $stmt->bindParam(":siteID", $siteID);
    $stmt->bindParam(":fromDate", $fromDate);
    $stmt->bindParam(":toDate", $toDate);
    $stmt->execute();

    foreach ($stmt as $row)
    {
        $timeDiff = date_diff(new DateTime($row['start_time']),new DateTime($row['end_time']), true); //force absolute

        if(!isset($maxATB) OR dateIntervalInSeconds($timeDiff) > dateIntervalInSeconds($maxATB))
            $maxATB = $timeDiff;
        if(!isset($minATB) OR dateIntervalInSeconds($timeDiff) < dateIntervalInSeconds($minATB))
            $minATB = $timeDiff;
        $totalATB->add($timeDiff);
        $totalEvents++;
    }

    if($totalEvents!=0)
    {
        //$avgATB=round($totalATB->getTimestamp() / $totalEvents);
            $avgATB = average_time($totalATB->format("H:i:s"),$totalEvents,0);
    }
    else
    {
        $avgATB=0;
        $maxATB=new DateInterval('PT0S');
        $minATB=new DateInterval('PT0S');
    }
    $avgSeconds = new DateInterval("PT" . $avgATB . "S");
    $ATBarray['max'] = $maxATB->format("%H:%I:%S");
    $ATBarray['min'] = $minATB->format("%H:%I:%S");
    $ATBarray['avg'] = gmdate("H:i:s",$avgATB); //$avgSeconds->format("%H:%i:%s");
    $ATBarray['total'] = $totalATB->format("H:i:s");
    $ATBarray['events'] = $totalEvents;

    return $ATBarray;

}

Unfortunately, I'm getting extremely high averages. As an example, I'm finding that the max time is 3 seconds, the min time is 0 seconds, but the average time is 1 hour and 1 second. This is obviously impossible, so is there something wrong with how I'm calculating total and/or average? The total is rather high too, but I haven't been able to manually add up this data so I'm not sure if that is also incorrect.

Upvotes: 0

Views: 479

Answers (2)

muttley91
muttley91

Reputation: 12674

EDIT:

I've changed the way I'm calculating averages. I've added the new function all above, and here is the averaging function (found here):

function average_time($total, $count, $rounding = 0) //from SO
{
    $total = explode(":", strval($total));
    if (count($total) !== 3) return false;
    $sum = $total[0]*60*60 + $total[1]*60 + $total[2];
    $average = $sum/(float)$count;
    $hours = floor($average/3600);
    $minutes = floor(fmod($average,3600)/60);
    $seconds = number_format(fmod(fmod($average,3600),60),(int)$rounding);

    if($hours<10) $hours = "0"+$hours; //add leading 0
    if($minutes<10) $minutes = "0"+$minutes;
    if($seconds<10) $seconds = "0"+$seconds;
    return $hours.":".$minutes.":".$seconds;
}

So now my average numbers have changed and I'm trying to check the totals to see if they're correct. But I've posted that as another question, located here.

Upvotes: 0

fbitterlich
fbitterlich

Reputation: 913

This is most probably a DST problem. Is your code running in a U.S. timezone? Rerember that when you're using new DateTime("@0"), this represents 1970-01-01 00:00:00 (non-DST); in the U.S., DST has started now, so the absolute value of your DateTime objects created from the database are one hour off.

Upvotes: 1

Related Questions