Re: Awkward time processing

[Paul Halliday <paul.halliday@xxxxxxxxx>]

On Thu, Aug 2, 2012 at 12:27 PM, Ashley Sheridan
<ash@xxxxxxxxxxxxxxxxxxxx> wrote:
>
>
> Paul Halliday <paul.halliday@xxxxxxxxx> wrote:
>
>>This is hideous, can anyone think of a more novel approach?
>>
>>What I have is an array of values and timestamps:
>>
>>17 15:31
>>16 15:32
>>27 15:33
>>14 15:34
>>11 15:35
>>
>>now for a day I should have 1440 entries but there could be spotty
>>results, no data from say 11:59 -> 13:00.
>>What I need is to sum the values for each hour interval.
>>
>>Because the results could be spotty I figured I would need to use a
>>lookup table so that I could zero out any empty hours (this is going
>>to be graphed)
>>
>>So I have this:
>>
>>loop through the set above and do
>>
>>$hour = explode(":", $row[0]);
>>$comp[] = $hour[0] . "||" . $row[1];
>>
>>$c00 = $c01 = $c02 = $c03 = $c04 .... = 0;
>>
>>for ($a = 0; $a < sizeof($comp); ++ $a) {
>>    list($h,$c) = explode("||", $comp[$a]);
>>
>>    switch ($h) {
>>        case 00: $c00 += $c; break;
>>        case 01: $c01+= $c; break;
>>        case 02: $c02 += $c; break;
>>        case 03: $c03 += $c; break;
>>        case 04: $c04 += $c; break;
>>        .....
>>    }
>>}
>>
>>Works but wow is it ugly..
>>
>>Thoughts?
>>
>>--
>>Paul Halliday
>>http://www.pintumbler.org/
>>
>>--
>>PHP General Mailing List (http://www.php.net/)
>>To unsubscribe, visit: http://www.php.net/unsub.php
>
> Where is the data coming from? I would presume an SQL database? If so, you could use a group by with a
substring to get the counts you need. MySQL happily allows this to be
done on date and datetime fields.

I am actually doing that already on the data coming in:

SELECT COUNT(signature) AS count,
SUBSTRING(CONVERT_TZ(timestamp,'+00:00','-03:00'),12,5) AS time..

as I am doing a minute breakdown as well. The query isn't cheap; about
half a second, so I was hoping to do my hourly consolidation in code
as I already have the data, instead of performing a second query.

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php