How to calculate seconds difference of time format:yyyyMMdd HH:mm:ss? For example,calculate seconds difference of 20190102 00:01:05 and 20190102 02:14:18

1 Answers

3
leftjoin On Best Solutions

Use UNIX_TIMESTAMP function to convert timestamps to seconds, then subtract:

select UNIX_TIMESTAMP('20190102 02:14:18','yyyyMMdd HH:mm:ss') -
       UNIX_TIMESTAMP('20190102 00:01:05','yyyyMMdd HH:mm:ss');

Returns:

7993 seconds.

Difference in 'HH:mm:ss' format:

select from_unixtime(UNIX_TIMESTAMP('20190102 02:14:18','yyyyMMdd HH:mm:ss') -
       UNIX_TIMESTAMP('20190102 00:01:05','yyyyMMdd HH:mm:ss'), 'HH:mm:ss');

Returns:

02:13:13