sql - Calculate difference between start_time and end_time in seconds from unix_time yyyy-MM-dd HH:mm:ss

Question

I'm still learning SQL and I found a couple of solutions on SQL Server or Postgreы, but it doesn't seen to work on HUE DATEDIFF, only allows me to calculate difference between days seconds, minutes are not available. Help is very welcome.

I was able to split the timestamp with substring_index, but then I can't find the right approach to compare and subtract start_time to end_time in order to obtain the accurate account of seconds. I can't find time functions so I'm assuming I should calculate it based on timestamp. obtained as

from_unixtime(unix_timestamp(start_time, "yyyy-MM-dd'T'HH:mm:ss.SSSSSS"), 'yyyy-MM-dd HH:mm:ss')


substring_index(start_time, 'T', -1)s_tm,
substring_index(end_time, 'T', -1)e_tm


start_date 2018-06-19 13:59:41  
end_date   2018-06-19 14:01:17

desired output

01:36

Answer 1

Solution for Hive.

Difference in seconds:

select UNIX_TIMESTAMP('2018-06-19T14:01:17.000000',"yyyy-MM-dd'T'HH:mm:ss.SSSSSS")-
   UNIX_TIMESTAMP('2018-06-19T13:59:41.000000',"yyyy-MM-dd'T'HH:mm:ss.SSSSSS") as seconds_diff

Result:

96

Now calculate difference in HH:mm:ss:

select concat_ws(':',lpad(floor(seconds_diff/3600),2,'0'),        --HH
                     lpad(floor(seconds_diff%3600/60),2,'0'),     --mm
                     lpad(floor(seconds_diff%3600%60),2,'0')      --ss
       )

from
(
select --calculate seconds difference
       UNIX_TIMESTAMP('2018-06-19T14:01:17.000000',"yyyy-MM-dd'T'HH:mm:ss.SSSSSS")-
       UNIX_TIMESTAMP('2018-06-19T13:59:41.000000',"yyyy-MM-dd'T'HH:mm:ss.SSSSSS") as seconds_diff
) s

Result:

OK
00:01:36
Time taken: 1.071 seconds, Fetched: 1 row(s)

See also this answer about format convertion: https://stackoverflow.com/a/23520257/2700344