I'm trying to get an average and a total for timeonhold and talktime.
Right now they are stored as "time" variables in MYSQL but I don't think that is correct, that is more for time of day right? Either way the AVG and SUM functions aren't working as if they are time, for instance this select...
SELECT SUM(talktime) FROM data WHERE callcenter='yes' AND station='375' AND queue='241'
Is returning 176, which is sort of meaningless. Kind of close to seconds, except it is counting 1:18 as 118 which doesn't work. How would I be able to get a real sum and average using SQL?
OR... would it be a better solution to just modify the data (in PHP) before it gets imported? I wrote a simple PHP import page that takes the data as is, it wouldn't be too difficult to convert into seconds or something, but ideally I'd prefer the database reflect the actual data as much as possible and go from there.
TIME can be used to store both time of day and durations.
Maybe it would work to convert the values to seconds using time_to_sec(), take the avg or sum, then convert back to TIME with sec_to_time(). Another option would be to just store them in seconds as ints, and do the conversion either with the mysql function or in your PHP.
SELECT sec_to_time(sum(time_to_sec(fieldname))) ....