Date diff in hive and the difference should be in hh:mm:ss

Question

I am trying to find the difference between two dates in successive rows. I am using the windowing functions in hive i.e, lag.

But the difference i.e., the output should be in the format hh:mm:ss.

For eg:

• Date 1 is 2017-08-15 02:00:32
• Date 2 is 2017-08-15 02:00:20

Output should be:

00:00:12

The query I tried with:

select from_unixtime(column_name),
(lag(unix_timestamp(from_unixtime(column_name)),1,0)
over(partition by column_name)-
unix_timestamp(from_unixtime(column_name))) as Duration from table_name;

But this returns the output as 12 (in the above example).

Update

I have stored the column in the table with bigint datatype. The time is in epoch format. We are converting it to readable date by using from_unixtime in our query. Sample values in timestamp

1502802618 1502786788

Answer 1

hive> with t as (select 1502802618 as ts1,1502786788 as ts2)
    > select  printf('%02d:%02d:%02d',(ts1 - ts2) div 3600,((ts1 - ts2) % 3600) div 60,((ts1 - ts2) % 3600) % 60) as diff
    > from    t
    > ;
OK
diff
04:23:50

Answer 2

The answer will be relevant as long as the times difference is less than 24 hours

hive> with t as (select 1502802618 as ts1,1502786788 as ts2)
    > select  from_unixtime(to_unix_timestamp('0001-01-01 00:00:00')+(ts1 - ts2))  as diff
    > from    t
    > ;
OK
diff
0001-01-01 04:23:50

---

hive> with t as (select 1502802618 as ts1,1502786788 as ts2)
    > select  substr(from_unixtime(to_unix_timestamp('0001-01-01 00:00:00')+(ts1 - ts2)),12)   as diff
    > from    t
    > ;
OK
diff
04:23:50

Answer 3

The answer will be relevant as long as the times difference is less than 24 hours

hive> with t as (select timestamp '2017-08-15 02:00:32' as ts1,timestamp '2017-08-15 02:00:20' as ts2)
    > select  ts1 - ts2   as diff
    > from    t
    > ;
OK
diff
0 00:00:12.000000000

Given Timestamps

hive> with t as (select timestamp '2017-08-15 02:00:32' as ts1,timestamp '2017-08-15 02:00:20' as ts2)
    > select  split(ts1 - ts2,'[ .]')[1]  as diff
    > from    t
    > ;
OK
diff
00:00:12

Given strings

hive> with t as (select '2017-08-15 02:00:32' as ts1,'2017-08-15 02:00:20' as ts2)
    > select  split(cast(ts1 as timestamp) - cast(ts2 as timestamp),'[ .]')[1]  as diff
    > from    t
    > ;
OK
diff
00:00:12