0

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

Code Heaven
  • 11
  • 2

1 Answers1

0

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;
I_Al-thamary
  • 3,385
  • 2
  • 24
  • 37
  • Thanks for your quick response. We are using Apache Hive Version 3.1.3000.7.1.8.41-1. I didn't find TIMESTAMPDIFF function – Code Heaven Aug 17 '23 at 08:41
  • Also in Impala actual time difference in millisecond is just 147 millseconds .The query gives difference output which may not be correct SELECT CAST('2023-01-26T04:01:48.010' AS TIMESTAMP) AS T1, CAST('2023-01-26T04:01:47.863' AS TIMESTAMP) AS T2, UNIX_TIMESTAMP(CAST('2023-01-26T04:01:48.010' AS TIMESTAMP)) - UNIX_TIMESTAMP(CAST('2023-01-26T04:01:47.863' AS TIMESTAMP)) AS time_diff_seconds, (UNIX_TIMESTAMP(CAST('2023-01-26T04:01:48.010' AS TIMESTAMP)) - UNIX_TIMESTAMP(CAST('2023-01-26T04:01:47.863' AS TIMESTAMP))) * 1000 AS time_diff_milliseconds – Code Heaven Aug 17 '23 at 08:42
  • SELECT FROM_UNIXTIME(UNIX_TIMESTAMP('2023-01-26T04:01:48.010', 'yyyy-MM-dd HH:mm:ss.SSS'), 'yyyy-MM-dd\'T\'HH:mm:ss.SSS') AS formatted_t1 this query also gives null in HIVE and IMPALA. Please help me on this – Code Heaven Aug 17 '23 at 09:08
  • The `CAST` and `FROM_UNIXTIME` functions are available in both Hive and Impala where you need to convert it to `'yyyy-MM-dd\'T\'HH:mm:ss.SSS'` – I_Al-thamary Aug 17 '23 at 15:52