Averaging times in PHP that are returned from MySQL query

Question

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

Why are you not dealing with these durations (not "times") in seconds? — Lightness Races in Orbit, Oct 01 '14 at 18:58
is `$results->[6]` a string? Because from what you are saying that would likely be your problem with array_map requiring an array. — Jonathan Kuhn, Oct 01 '14 at 19:00
Seems like it would be a zillion times more efficient to aggregate this in the DB. Something like `SELECT sec_to_time(avg(time_to_sec())) from ` — JNevill, Oct 01 '14 at 19:04
I'm just learning this. I wish I was an expert. But I'm not. I have tried using the function found [link]http://www.stackoverflow.com/questions/10557076/adding-two-time-values-of-similar-formats-using-php[/link] and that didn't work. I tried converting to integers. I have literally been working on this 6 hours now before posting as I was sure the answer is out there. I just can't seem to find it. — David, Oct 01 '14 at 19:05
I would start by breaking this one line into multiple lines, in en effort to 'separate concerns' and make this easier to debug. From there, you can probably find where this equation is straying from your goal behavior. — Alex Villa, Oct 01 '14 at 19:07
JNevil is equal to the column the time is stored in? Then just echo the $results? — David, Oct 01 '14 at 19:07
http://stackoverflow.com/questions/2217139/mysql-average-on-time-column — Len_D, Oct 01 '14 at 19:10
@David Yes, that's what I was thinking. is the column that the time type field is stored in. I will write as answer, since this seems like a reasonable approach. — JNevill, Oct 01 '14 at 19:13

score 0 · Answer 1 · answered Oct 01 '14 at 19:15

0

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>

answered Oct 01 '14 at 19:15

JNevill

46,980
4
38
63

I have been trying that and echo $result but I get nothing. If I echo date('h:i:s', $result) I still am not getting anything. My column is old_time so I have `SELECT sec_to_time(avg(time_to_sec())) from TABLE_HTA; $result; echo $result; echo date('H:i:s', $result);` Yes the field otd_time is a time type in the mysql. – David Oct 01 '14 at 19:27
Is the field in the table a "Time" type? – JNevill Oct 01 '14 at 19:29
The field in the table displays as 00:31:36 00:32:58 00:11:59 – David Oct 01 '14 at 19:35
Just in case that is time stores as a string, try: `SELECT sec_to_time(avg(time_to_sec(str_to_date(, '%H:%k:%s')))) from ` That will convert the potential String field to TIME format, then convert that to seconds, then take the average, then return it as TIME again.
– JNevill Oct 01 '14 at 19:39
That results in parse error: syntax error, unexpected ':' – David Oct 01 '14 at 19:49
I went into phpmyadmin and ran the query `'SELECT SEC_TO_TIME(AVG(TIME_TO_SEC(otd_time))) FROM ' . TABLE_HTA . ' WHERE date_time >= CURDATE() AND employee_id = "4104"` and the result was 00:25:39 so I am loosing something on the echo $result. – David Oct 01 '14 at 20:07
here is my code for the php query `= CURDATE() AND employee_id = "4104"'; $otdAverage = $db->Execute($sql1); echo $otdAverage; ?>` Anyone see what is wrong. It works just fine in the phpmyadmin. – David Oct 01 '14 at 20:09

David · Accepted Answer · 2014-10-02T03:37:04.597

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!

Averaging times in PHP that are returned from MySQL query

2 Answers2