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?
Thanks
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
