CODEWITHSUNDEEP
sqlmysqltimestamp
Good Guy
Good Guy

Reputation: 972

MySQL: difference between two timestamps in seconds?

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.

Upvotes: 53

Views: 79998

Answers (3)

Santhosh Gandhe
Santhosh Gandhe

Reputation: 6960

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')

Upvotes: 2

OderWat
OderWat

Reputation: 5709

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!

Upvotes: 168

OMG Ponies
OMG Ponies

Reputation: 332531

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

Upvotes: 42

Related Questions