HIVE:how to calculate seconds difference of time format: yyyyMMdd HH:mm:ss

Question

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

Answer 1

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

Also you can use solution how to format seconds in 'HH:mm:ss' using explicit math proposed in this answer: https://stackoverflow.com/a/57497316/2700344