1

I have a data like this

id  timestamp       ticket_id
2   6/16/16 8:14    1466089815
10  6/27/16 7:15    1466089815
3   6/16/16 8:14    1466089816
11  6/27/16 7:15    1466089816
13  6/27/16 7:50    1466089816

I am trying to find the time taken by tiket_id. Is there a simple function which can be used to a groupby and take a time difference.

Here I am trying to find the time taken by the ticket_id. Eg:

ticket_id    time_taken
1466089816   10.93

Appreciate the support

Best Jagan

  • Time difference between what? You've got one time listed per ID, meaning a total of two times for ticket 1466089815 and three for ticket 1466089816. Your expected results (and possibly the rationale for them) would be helpful. – RDFozz Jun 23 '17 at 19:31
  • @RDFozz Here is the result I am trying .Here I am trying to find the time taken by the ticket_id. Eg: ticket_id time_taken 1466089816 10.93 . I have updated the question – Jaganadh Gopinadhan Jun 23 '17 at 19:49
3

If you want to find the date/time difference between the first and the last timestamp of each ticket, there are several functions that can help you:

select 
    ticket_id,
    timediff(max(timestamp), min(timestamp))              as diff,
    timestampdiff(hour, min(timestamp), max(timestamp))   as diff_in_hours,
    timestampdiff(minute, min(timestamp), max(timestamp)) as diff_in_minutes,
    timestampdiff(second, min(timestamp), max(timestamp)) as diff_in_seconds
from tickets
group by ticket_id ;

Test in dbfiddle.uk:

select ticket_id,
       timediff(max(timestamp), min(timestamp)) as diff,
       timestampdiff(hour, min(timestamp), max(timestamp)) as diff_in_hours,
       timestampdiff(minute, min(timestamp), max(timestamp)) as diff_in_minutes,
       timestampdiff(second, min(timestamp), max(timestamp)) as diff_in_seconds
from tickets
group by ticket_id ;
 ticket_id | diff      | diff_in_hours | diff_in_minutes | diff_in_seconds
---------: | :-------- | ------------: | --------------: | --------------:
1466089815 | 263:01:00 |           263 |           15781 |          946860
1466089816 | 263:36:00 |           263 |           15816 |          948960
  • Thanks. That is a cool solution. I will expand this code to resolve my issue. Appreciate the wonderful support. – Jaganadh Gopinadhan Jun 23 '17 at 19:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.