xvszero
07-09-2009, 11:45 AM
Here is a sample of my dataset...
+------+----------+------------+-----------+------+------+---------------+-----------------+---------+-------+---------+------------+----------+-----------+-----------+----------+------------+
| id | queue | station | timeonhold | talktime | callcenter |
+------+----------+------------+-----------+------+------+---------------+-----------------+---------+-------+---------+------------+----------+-----------+-----------+----------+------------+
| 3436 | 241 | 375 | 00:00:37 | 00:00:51 | yes |
| 3466 | 241 | 375 | 00:00:47 | 00:01:18 | yes |
| 3482 | 241 | 375 | 00:00:41 | 00:00:07 | yes |
+------+----------+------------+-----------+------+------+---------------+-----------------+---------+-------+---------+------------+----------+-----------+-----------+----------+------------+
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.
Any suggestions?
+------+----------+------------+-----------+------+------+---------------+-----------------+---------+-------+---------+------------+----------+-----------+-----------+----------+------------+
| id | queue | station | timeonhold | talktime | callcenter |
+------+----------+------------+-----------+------+------+---------------+-----------------+---------+-------+---------+------------+----------+-----------+-----------+----------+------------+
| 3436 | 241 | 375 | 00:00:37 | 00:00:51 | yes |
| 3466 | 241 | 375 | 00:00:47 | 00:01:18 | yes |
| 3482 | 241 | 375 | 00:00:41 | 00:00:07 | yes |
+------+----------+------------+-----------+------+------+---------------+-----------------+---------+-------+---------+------------+----------+-----------+-----------+----------+------------+
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.
Any suggestions?