TAGS :Viewed: 5 - Published at: a few seconds ago

[ Averaging times in PHP that are returned from MySQL query ]

I have 3 times returned from a query in the time format they are: 00:31:36 00:32:58 00:11:59

I want to get an average of these times in the same format.

currently I am trying to use

date('H:i:s', array_sum(array_map('strtotime', $results->[6])) / count($results->[6]))

But the output I am getting is 19:00:00 If I increase the output to 'm/d/y H:i:s' I am getting 12/31/69 19:00:00. If I delete the 'strtotime', I still get the same output.

Thanks for your help

Answer 1


Instead of relying on PHP to perform the AVG aggregation, instead use the DB where it is much more efficient:

SELECT sec_to_time(avg(time_to_sec(<time>))) from <table>

Answer 2


Solution that works for me

<?php  $totalTime = 0;
        $sql = 'SELECT * FROM ' . TABLE_HTA . ' WHERE date_time >= curdate() AND employee_id = "4104" ORDER BY order_id ASC';

        $orders = $db->Execute($sql);
        if($orders->RecordCount() > 0) {
        while(!$orders->EOF) {
        $totalRuns = $totalRuns + 1;
        $str_time = $orders->fields['otd_time'];
        $str_time = preg_replace("/^([\d]{1,2})\:([\d]{2})$/", "00:$1:$2", $str_time);
        sscanf($str_time, "%d:%d:%d", $hours, $minutes, $seconds);
        $time_seconds = $hours * 3600 + $minutes * 60 + $seconds;
        $orders->MoveNext();
            }
           }
        $otdAverage = $totalTime / $totalRuns;
        echo gmdate("H:i:s", $otdAverage);?>

input was:
00:31:36
00:32:58
00:11:59
00:22:52
00:32:08
00:22:19
00:51:46
The output was 00:29:22
phpMyAdmin sql query output was 00:29:22
So a difference of 1 second due to rounding somewhere. Thanks for your help. Hope this helps someone else!