Hive解决方案。
差异(以秒为单位):
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
结果:
96
现在计算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
结果:
OK
00:01:36
Time taken: 1.071 seconds, Fetched: 1 row(s)
另请参阅有关格式转换的以下答案:https ://stackoverflow.com/a/23520257/2700344