Вычислить разницу между start_time и end_time в секундах от 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


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

желаемый результат

01:36


sql hive hiveql seconds hue
person user11925123    schedule 14.08.2019    source источник

Ответы (1)


arrow_upward
0
arrow_downward

Решение для 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

Теперь рассчитайте разницу в формате ЧЧ: мм: сс:

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

person leftjoin    schedule 14.08.2019
comment
Спасибо, но таким образом я получаю для всех строк один и тот же результат, и если я попытаюсь заменить дату на column_name, как показано ниже, это не сработает. Не уверен, что мне не хватает чего-то очевидного ... from_unixtime (UNIX_TIMESTAMP (end_time) -UNIX_TIMESTAMP (start_time), 'mm: ss') - person user11925123; 14.08.2019
comment
@ user11925123, если он равен NULL, то, скорее всего, формат не такой, как в вашем примере: 2018-06-19 13:59:41. Поэтому я дал вам ссылку на ответ о преобразовании формата. Все, что вам нужно, это указать правильный формат для UNIX_TIMESTAMP в качестве второго параметра - person leftjoin; 14.08.2019
comment
Привет, исходный формат - гггг-ММ-дд'Т'ЧЧ: мм: сс.СССССС. Когда я использую from_unixtime (unix_timestamp (end_time, yyyy-MM-dd'T'HH: mm: ss.SSSSSS), 'mm') - from_unixtime (unix_timestamp (start_time, yyyy-MM-dd'T'HH: mm: ss .SSSSSS), 'мм') он работает, если конечный и начальный час совпадают, однако, когда конечный час равен 17:16:04, а начальный час 16:58:05, вычитание неверно. Если я попытаюсь использовать с ЧЧ: ММ вместо просто ММ, я получу ноль. Какие-нибудь советы? Заранее спасибо. - person user11925123; 17.08.2019
comment
@ user11925123 Да, преобразование формата работает странно. Используйте явную математику. - person leftjoin; 17.08.2019
comment
@substract секунд, затем вычислить ЧЧ, мм, сс. Увидеть фиксированный ответ - person leftjoin; 17.08.2019
comment
@ user11925123 Добро пожаловать! Пожалуйста, не забудьте принять / проголосовать - person leftjoin; 17.08.2019
comment
Привет снова, leftjoin, у меня проблемы с получением агрегированных результатов, так как вывод - это строка, и я думаю, что мне нужен INT. Я попытался преобразовать его в int, но я снова получаю часы, минуты и секунды, разделенные на разные столбцы. Я мог бы использовать всего несколько минут, но в среднем мне понадобится все это за один раз. Извините за вопрос, но я просто не вижу выхода из этого. Я пытаюсь какое-то время. Есть ли какой-нибудь совет, который вы могли бы дать, как упростить работу с истекшим временем, рассчитываемым медианой, средним значением и т. Д.? Спасибо заранее! - person user11925123; 18.08.2019
comment
@ user11925123 Используйте разницу в секундах (см. первый запрос), объедините ее по своему усмотрению и после объединения преобразуйте в строку HH: mm: ss - person leftjoin; 18.08.2019