Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2005
    Posts
    7

    Unanswered: Average Response Time Logic

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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 )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2005
    Posts
    7

    Talking Thank you

    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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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