I have a column, duration, that represents a time Interval in Milliseconds.
It was previously calculated and then converted to BIGINT using to_milliseconds in order to save the results, since Hive doesn't accept Interval type.
Now, I'd like to convert it back to an Interval. I'm aware that I can use date_add('millisecond', duration, ts_col), but I'd prefer to be able to use the timestamp + duration format that an Interval allows for.
The workaround I came up with is: parse_duration(CAST(cs.duration AS VARCHAR) || 'ms'), but this seems like it'll be rather inefficient...
Is there a better/built-in method somewhere in the documentation that I'm missing?
SELECT duration
--, CAST(duration AS INTERVAL MILLISECOND)
, parse_duration(CAST(duration AS VARCHAR) || 'ms') AS duration_interval
, date_add('millisecond', duration, event_start_time) AS next_event
, event_start_time + parse_duration(CAST(duration AS VARCHAR) || 'ms') AS next_event_interval
FROM events
| duration | duration_interval | next_event | next_event_interval |
|---|---|---|---|
| 1545 | 0 00:00:01.545 | 22-10-30 01:22:33.2233 | 22-10-30 01:22:33.2233 |
| 184 | 0 00:00:00.184 | 22-10-30 01:22:34.2234 | 22-10-30 01:22:34.2234 |
| 5033 | 0 00:00:05.033 | 22-10-30 01:22:39.2239 | 22-10-30 01:22:39.2239 |
| 1592 | 0 00:00:01.592 | 22-10-30 01:22:40.2240 | 22-10-30 01:22:40.2240 |
| 1011 | 0 00:00:01.011 | 22-10-30 01:22:29.2229 | 22-10-30 01:22:29.2229 |
| 2982 | 0 00:00:02.982 | 22-10-30 01:22:32.2232 | 22-10-30 01:22:32.2232 |
| 295 | 0 00:00:00.295 | 22-10-30 01:22:28.2228 | 22-10-30 01:22:28.2228 |
| 2556 | 0 00:00:02.556 | 22-10-30 01:22:43.2243 | 22-10-30 01:22:43.2243 |
| 687 | 0 00:00:00.687 | 22-10-30 01:22:44.2244 | 22-10-30 01:22:44.2244 |
| 3635 | 0 00:00:03.635 | 22-10-30 01:22:47.2247 | 22-10-30 01:22:47.2247 |
The best method I could come up with, after some input from Guru, is:
date_add('millisecond', duration, TIME '00:00:00.000')