CODEWITHSUNDEEP
hivehiveqlcloudera-cdh
Hardik Gupta
Hardik Gupta

Reputation: 4790

Difference between two timestamp in Hive through query

I have a hive table with following columns

    session     Timestamp
    1           2018-05-18T00:00:00.000Z
    1           2018-05-18T00:01:00.000Z
    1           2018-05-18T00:02:00.000Z
    1           2018-05-18T00:03:00.000Z
    2           2018-05-18T00:05:00.000Z
    2           2018-05-18T00:10:00.000Z
    2           2018-05-18T00:15:00.000Z

For each session, I want to find the difference between the first timestamp and last timestamp, in seconds

Hence my output will be like this

    session     Period
    1           180
    2           600

The 1st difference, 180 (seconds) = 2018-05-18T00:03:00.000Z - 2018-05-18T00:00:00.000Z

The 2nd difference, 600 (seconds) = 2018-05-18T00:15:00.000Z - 2018-05-18T00:05:00.000Z

Both session and Timestamp is in string.

I am trying to convert string timestamp into unix timestamp using this command, however I am not successful

    select from_unixtime(unix_timestamp(Timestamp, "yyyy-MM-dd'T'HH:mm:ss.SSSZ")) AS NEW_TIMESTAMP from TBL;

Upvotes: 0

Views: 8076

Answers (1)

mazelx
mazelx

Reputation: 142

unix_timestamp converts a string to timestamp (seconds), so you just need to use this function and substract the min from the max :

select 
max(unix_timestamp(Timestamp, "yyyy-MM-dd'T'HH:mm:ss.SSSZ"))
-
min(unix_timestamp(Timestamp, "yyyy-MM-dd'T'HH:mm:ss.SSSZ"))
from TBL
group by session_id

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF

Upvotes: 1

Related Questions