从 unix_time yyyy-MM-dd HH:mm:ss 计算 start_time 和 end_time 之间的差异(以秒为单位) [英] 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") as 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)从(select --calculate 秒差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

结果:

好的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 计算 start_time 和 end_time 之间的差异(以秒为单位)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！