CODEWITHSUNDEEP
sqlhadoophiveimpala
Saksham Arora
Saksham Arora

Reputation: 71

Timestamp operations in hive

How can I subtract 2 timestamp columns in hive and store the result in a separate column in its equivalent hours format?

Upvotes: 2

Views: 955

Answers (2)

stl_fcc
stl_fcc

Reputation: 11

Use the unix_timestamp function to convert a hive timestamp to a number of seconds since epoch. Subtracting the unix timestemp results gives an answer in seconds.

    SELECT start_dttm,
             (unix_timestamp(end_dttm) - unix_timestamp(start_dttm))/60 AS duration_in_minutes
    FROM dev_edw.audit_history_hb
    WHERE script_name LIKE '%0_hive_d%'
            AND parent_audit_id is null
    ORDER BY start_dttm desc

Upvotes: 0

Nishu Tayal
Nishu Tayal

Reputation: 20820

Let's say if you have timestamp in the given format : 2016-10-16 10:51:00.000

You can try following:

SELECT
  cast(
    round(
      cast((e-s) as double) * 1000
    ) as int
  ) time_difference
FROM (SELECT cast(starttime as double) s, cast(endtime as double) e from table1) q;

It will give you the difference of both timestamps in millisecond. Then you can convert it to your expected format(hours,days etc.) .

Upvotes: 2

Related Questions