SUM of char column in Query?

Tbull

I am a newbie who could use guidance. Must be a better way, hopefully in query using group by?

Have MySQL db with hundreds of thousands of event records.

The columns and records look like;

Col1 Char, Col2 DATE, Col3 Char
EventType1, YYYY-MM-DD HH:MM:SS, ##d HH:MM:SS
EventType2, YYYY-MM-DD HH:MM:SS, ##d HH:MM:SS
EventType1, YYYY-MM-DD HH:MM:SS, ##d HH:MM:SS
EventType1, YYYY-MM-DD HH:MM:SS, ##d HH:MM:SS
...
etc.

The query is assembled from passed parameters and in its simplest form looks something like;

Select Col1, Col2, Col3 from events where (col2>=StDate and col2<EnDate) group by Col1 order by Col1, Col2;

The desired output results is html'd/tabled to;

EventType1, sum of Col3 (for EventType1) is xxx Hours, xx Minutes and xx Seconds
EventType2, sum of Col3 (for EventType2) is xxx Hours, xx Minutes and xx Seconds
EventN+1 etc.
...
725,334 events occured for a total of XXXXX Hours, XXX Minutes and XXX Seconds

Isn't there a way to perform the summing for the table cells of hrs,mins and seconds of col3 in the query process? Walking through the output of thousands of records is extremely high overhead. OBTW, col3 is char not numeric and is passed from a logging device.

Thanks to anyone who can offer suggestions.

🙂

Kerry_Kobashi

What's the purpose of column 3?
Is it elapsed time?

What I am thinking is if it is elapsed time, you can cut the granularity down to say, minutes or seconds, then store this as a long integer. Then do a SUM on the column 3. (i.e. change the database schema for column 3 to be a long integer, represent elapsed time)

Kerry Kobashi
Kobashi Computing

Tbull

Thank you for your response and keen insight.

Col3 is duration (or elapsed time) of the event. My apology for not having clearly indicated that and that the ##d is days. If the event is less than a day it has 0d in it. So it becomes somewhat problematic to alter the schema or field. Not to mention that the logger is determined to output the field as shown, and for charging purposes the granularity is required.

Thank you again.

Kerry_Kobashi

So given the above, I am assuming that you cannot change the logger program (a separate program) to output time elapsed in a different granularity.....

If you store the data as a character based time/date character format, I would think there would have to be translation into this granularity anyway.

I don't think there is a way to get mySQL to do the sum() for you - it would have to be an intelligent polymorphic sum function that knows how to deal with time/date and know how to take on the meaning the way you represent it. To make things even more interesting, it would have to know this is elapsed time, not clock time... see the delimna?

I don't see an easier way to get around this given that the source of your problem appears to be the logger program storing data in a unwieldy character based format. And yes, I can see the big overhead if you have lots of records of having to go through each one on the SELECT and do the conversion for column3 the hard way.

Sorry, I don't see an easier way to do this. If you find it, please let me know!

Kerry Kobashi
Kobashi Computing

P.S.
I appreciate the effort you took in presenting your question so that you can be understood.

Tbull

Kerry,
You are correct in that the logger output is from other programs (devices - in fact they are geographically dispersed). In a period <7 days the log records were 125,000+.

Your summary of the problem is the one that I have been beating against the wall to resolve. I am going to explore preprocessing the records in some other manner, as that seems to be the only possible avenue of resolution.

Thank you for your thoughts, suggestions and the compliment concerning clarity in presenting the problem.

Best,
TBull

Tbull

Nearly solved. Have addressed data by changing parsing that feeds the database. What was col3 has been split so that col4 is a TYPE = TIME. Sums quite nicely and provides a numeric return.

The columns and records look like;

                     Col1 Char, Col2 DATE, Col3 Char, Col4 TIME 
                     EventType1, YYYY-MM-DD HH:MM:SS, ##d, HH:MM:SS 
                     EventType2, YYYY-MM-DD HH:MM:SS, ##d, HH:MM:SS 
                     EventType1, YYYY-MM-DD HH:MM:SS, ##d, HH:MM:SS 
                     EventType1, YYYY-MM-DD HH:MM:SS, ##d, HH:MM:SS 
                     ... 
                     etc.

As an example of a resulting sum of a few records col4 = 4095561. What is the SQL syntax to disp this result as HH:MM:SS?

The shell query is:
select cols..., (sum(col4)) as tmax from db where xyz group by Col1 order by col1, etc.

The tmax displays 4095561 -- that's what needs to be formed as HH:MM:SS.

Thanks to anyone with insight.