Geek Answers Handbook

Subtracting Timestamps

I thought it would be very simple, but it’s hard for me to find the answers!

I have a support table with added_onand fields closed_onthat track the opening and closing of support tickets.

I would like to run a request to (closed_on - added_on)show the duration of the ticket opening. Both fields have a format timestamp. It is not as easy as I thought it would be ...

Ideally, the end result would be X days Y hours Z minutes, but I would be happy just to get full seconds or something like that, of course, I can 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
+5
source share
2 answers

Using:

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 give me:

0 days 00 hours 00 minutes
0 days 13 hours 30 minutes
+3

TIMESTAMPDIFF, :

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

: - UNIX_TIMESTAMP:

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

More articles:


All Articles