List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:November 28 2002 11:24pm
Subject:Re: Any help? SUM of type TIME in Query?
View as plain text  
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

Thread
Any help? SUM of type TIME in Query?hturnbull29 Nov
  • Re: Any help? SUM of type TIME in Query?Paul DuBois29 Nov