从unix_time计算开始时间和结束时间之间的差异(以秒为单位)yyyy-MM-dd HH:mm:ss [英] Calculate difference between start_time and end_time in seconds from unix_time yyyy-MM-dd HH:mm:ss

问题描述

我仍在学习SQL，但我在SQL Server或Postgreы上找到了几种解决方案，但在HUE上似乎无法使用 DATEDIFF ，仅允许我计算两天之间的差额秒，分钟不可用.非常欢迎您的帮助.

我能够用 substring_index 分割时间戳，但是后来我找不到正确的方法来比较start_time并将其减去end_time以获得准确的秒数.我找不到时间函数，所以我假设我应该根据时间戳进行计算.获得为

  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开始日期2018-06-19 13:59:41截止日期2018-06-19 14:01:17 

所需的输出

01:36

解决方案

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")为seconds_diff 

结果:

  96 

现在计算HH:mm:ss:

的差异

  select concat_ws(':'，lpad(floor(seconds_diff/3600)，2，'0')，--HHlpad(floor(seconds_diff％3600/60)，2，'0')，--mmlpad(floor(seconds_diff％3600％60)，2，'0')--ss)从(选择-计算秒差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")为seconds_diff)s 

结果:

 确定00:01:36花费时间:1.071秒，获取:1行 

另请参阅以下有关格式转换的答案: https://stackoverflow.com/a/23520257/2700344

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

解决方案

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

这篇关于从unix_time计算开始时间和结束时间之间的差异(以秒为单位)yyyy-MM-dd HH:mm:ss的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！