Search code examples
sqlhadoophiveimpala

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?

Solution

  • 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.) .