At 15:18 -0800 11/28/02, hturnbull@stripped wrote:
>Have MySQL db with hundreds of thousands of event records from
>geographically dispersed logging devices. The logs are batched and
>auto loaded/parsed into MySQL on a daily/weekly basis depending on
>external factors. As an example, during a 7 period, one device
>provided 128,000 discrete events of 220 Types. The columns of
>primary interest are shown here. Col4 is generated by the logging
>devices as a duration/elapsed time in HH:MM:SS format and is the
>column of interest in this problem.
>
>An abbreviated table of the columns (ordered and numbered for
>example only) and records looks like;
>
>Col1(Char), Col2 (DATE), Col4(TIME)
>EventType1, YYYY-MM-DD HH:MM:SS, 09:53:05
>EventType1, YYYY-MM-DD HH:MM:SS, 05:56:39
>EventType1, YYYY-MM-DD HH:MM:SS, 05:53:36
>EventType1, YYYY-MM-DD HH:MM:SS, 01:11:09
>EventType1, YYYY-MM-DD HH:MM:SS, 22:48:12
>EventType2, YYYY-MM-DD HH:MM:SS, 01:11:32
>EventType2, YYYY-MM-DD HH:MM:SS, 22:48:46
>...
>etc. n+1
>
>The shell query is:
>
>select cols, count(xy), (sum(col4)) as t_dura
> from db
> where xyz
> group by Col1
> order by col1
>
>The results using the above as an example would be:
>
>Col1 Char, xy, t_dura
>EventType1, 5, 442201
>EventType2, 2, 235978
>...
> ... n+1
>
>Can anyone provide insight into the SQL syntax to perform the sum
>and return the results as HH:MM:SS?
Sounds like you want:
SEC_TO_TIME(SUM(TIME_TO_SEC(Col4)))
That is, convert each time to seconds, sum the result, convert back
to time.
>
>Thanks
