Average Response Time Logic

psalzmann · #1 (**permalink**) 02-24-05, 04:58

Hello

We would like to display our trouble ticket response times on our main front page. In our database, we have:

id | created | responded

1 | 2004-02-12 11:51:21 | 2004-02-12 15:29:31
etc etc
(30 rows)

I'm looking for a way to display something like:

(last 30 ticket response time)
1 hr : 03 min

Thanks in advance

r937 · #2 (**permalink**) 02-24-05, 05:58

Code:

select sec_to_time(
         avg(
            unix_timestamp(responded)
          - unix_timestamp(created)
             )
                  ) as avg_response_time
  from yourtable
 where created in
     ( select created 
         from yourtable
       order by created desc
       limit 30 )

psalzmann · #3 (**permalink**) 02-24-05, 08:05

It seems your query worked (the math logic)

excellent.

I think my version of MySQL [v3.23.58] is not letting me do the sub-query (I think) it is reporting errors.. Would this be the cause?

Other than that, I rewrote the query to:

Code:

SELECT sec_to_time( avg( unix_timestamp( responded )  - unix_timestamp( created )  )  )  AS avg_response_time
FROM troubleticket
WHERE priority = '3'
ORDER BY id DESC LIMIT 30

Returned: 38:05:25

I think that will do the same logic? or am I off a little? I'm wondering what the results would be if I was able to use your full query..

Thanks

Peter

r937 · #4 (**permalink**) 02-24-05, 08:17

Quote:

Originally Posted by psalzmann

Would this be the cause?

please see version 4.1 is the current version -- PLEASE READ THIS IF YOU'RE NOT ON 4.1 YET

as for your LIMIT 30, that's not right -- what you are saying is to take the average of all the rows in the table, come up with one number, sort it descending by id (impossible), and then take the first 30 rows of it (which would be easy, as there is only one row of it)

if you want the average of the last 30 rows, you first have to identify the last 30 rows

you could use 2 queries and pass the date of the 30th from the first to the second, or else use a temp table

Internet Services

Web Development

Digital Marketing

Consumer Tech