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 20开发者_Go百科10-01-03 00:00:01 2010-01-03 13:30:01
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
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)...
精彩评论