51

Is it possible to calculate difference between two timestamps in Mysql and get output result in seconds? like 2010-11-29 13:16:55 - 2010-11-29 13:13:55 should give 180 seconds.

Thank you

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Good Guy
  • 952
  • 2
  • 9
  • 7
  • A very similar question was asked a day ago: http://stackoverflow.com/questions/4289828/calculates-difference-between-two-dates-in-php/ – Orbling Nov 29 '10 at 02:20
  • 1
    @Orbling: No, the result is very different because the output is in hh:mm:ss – OMG Ponies Nov 29 '10 at 03:15
  • @OMG My answer to that question, the accepted one, is in hh:mm:ss - the other answers are not, hence it includes his answer. Which you have restated below. – Orbling Nov 29 '10 at 03:19

3 Answers3

164

I do not think the accepted answer is a good universal solution!

This is because the UNIX_TIMESTAMP() function fails for DATEs before 1970-01-01 (and for dates in the far future using 32 bit integers). This may happen easily for the day of birth of many living people.

A better solution is:

SELECT TIMESTAMPDIFF(SECOND, '2010-11-29 13:13:55', '2010-11-29 13:16:55')

Which can be modified to return DAY YEAR MONTH HOUR and MINUTE too!

OderWat
  • 5,379
  • 3
  • 29
  • 31
  • is their any way to convert the result into hr:min:sec format ? – coolDude Dec 17 '15 at 23:02
  • This works perfectly. However, the answer is reversed (second date - first date) is the answer. TIMESTAMPDIFF(YEAR, '2016-01-01 00:00:00', '2015-01-01 00:00:00') returns -1 not 1. Thanks – Kareem Feb 10 '16 at 12:20
  • 3
    @Kareem : The OP wanted 180 as result. That is what my example code gives. – OderWat Feb 10 '16 at 14:09
  • 4
    @coolDude: SELECT SEC_TO_TIME(TIMESTAMPDIFF(SECOND, '2010-11-29 13:13:55', '2010-11-29 13:16:55')) would do the trick. – OderWat Feb 10 '16 at 14:10
  • @OderWat thanks for the reply .. i solved the problem long time back SELECT SEC_TO_TIME(TIMESTAMPDIFF(SECOND, StartTime, EndTime)) as executionTime from abc – coolDude Feb 10 '16 at 18:10
  • @OderWat your example is perfect, I just wanted to make it clear to others that it is the other way around because it took me a few attempts till I worked it out. Cheers – Kareem Feb 11 '16 at 09:35
42

Use the UNIX_TIMESTAMP function to convert the DATETIME into the value in seconds, starting from Jan 1st, 1970:

SELECT UNIX_TIMESTAMP('2010-11-29 13:16:55') - UNIX_TIMESTAMP('2010-11-29 13:13:55') as output

Result:

output
-------
180

An easy way to deal with if you're not sure which value is bigger than the other -- use the ABS function:

SELECT ABS(UNIX_TIMESTAMP(t.datetime_col1) - UNIX_TIMESTAMP(t.datetime_col2)) as output
OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
2

TIMESTAMPDIFF method only works with datetime format. If you want the difference between just two times like '11:10:00' minus '10:20:00' then use

select TIME_TO_SEC('11:10:00')-TIME_TO_SEC('10:20:00')
Santhosh Gandhe
  • 6,900
  • 2
  • 23
  • 17
  • TIMESTAMPDIFF will work for 'time' data types. select TIMESTAMPDIFF(MINUTE , cast('08:22:03' as time), cast('09:59:03' as time)) – MikA Apr 27 '20 at 05:44