How to find time difference between two timestamps in seconds and milliseconds in hive and impala

Question

Need a help in finding time difference between two timestamps in seconds and milliseconds in hive and impala. We are using CDP cluster. Two columns are in string datatype with value in the format yyyy-MM-dd'T'HH:mm:ss.SSS

t1	t2
2023-01-26T04:01:49.010	2023-01-26T04:01:47.863
2023-01-26T04:01:48.999	2023-01-25T04:01:47.001

I tried "https://stackoverflow.com/questions/35329652/hive-timestamp-differences-in-milliseconds" but in this milliseconds are ignored or missed so it is not giving accurate results. Please help

Answer 1

To extract Unix timestamps, you can use the unix_timestamp and FROM_UNIXTIME functions. You need to ensure the format is yyyy-MM-dd HH:mm:ss.SSS.

SELECT
  t1,
  t2,
  CAST(t1 AS TIMESTAMP) AS timestamp1,
  CAST(t2 AS TIMESTAMP) AS timestamp2,
  FROM_UNIXTIME(UNIX_TIMESTAMP(timestamp2) - UNIX_TIMESTAMP(timestamp1), 'yyyy-MM-dd\'T\'HH:mm:ss.SSS') AS time_diff_milliseconds
FROM your_table;