sqlmysqltimestamp

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.

Solution

  • 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