subtracting timestamps

Question

I thought this would be a really simple thing, but I'm having a hard time finding any answers!

I have a support table, with added_on and closed_on fields that track when support tickets were opened and closed.

I'd like to run a query that (closed_on - added_on) to show the duration a ticket was open. Both fields are timestamp format. This is not as simple as I thought it would be...

Ideally, final output would be X days Y hours Z minutes but I'd be happy just to get total seconds or something, I can certainly take it from there.

Sample data:

[id]  [added_on]             [closed_on]
 1    2010-01-01 00:10:20    2010-01-02 00:10:20   
 1    2010-01-03 00:00:01    2010-01-03 13:30:01   

Answer 1

Use:

SELECT CONCAT(DATEDIFF(closed_on, added_on), 
              ' days ', 
              SUBSTRING_INDEX(TIMEDIFF(closed_on, added_on), ':', 1), 
              ' hours ', 
              SUBSTR(TIMEDIFF(closed_on, added_on), INSTR(TIMEDIFF(closed_on, added_on), ':')+1, 2),  
              ' minutes')

With your example data, that gives me:

0 days 00 hours 00 minutes
0 days 13 hours 30 minutes

Answer 2

You can use TIMESTAMPDIFF to get the difference in seconds:

SELECT TIMESTAMPDIFF(SECOND,closed_on,added_on)...

Edit: Another way would be to use UNIX_TIMESTAMP:

SELECT UNIX_TIMESTAMP(closed_on)-UNIX_TIMESTAMP(added_on)...