www.webdeveloper.com
Results 1 to 2 of 2

Thread: How to do SUM and AVG on time data?

  1. #1
    Join Date
    Jun 2007
    Posts
    400

    How to do SUM and AVG on time data?

    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...

    Code:
    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?

  2. #2
    Join Date
    Jun 2009
    Posts
    36
    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))) ....

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles