Trino/Presto CAST BIGINT (Milliseconds) to INTERVAL Datatype-CodePudding

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

CodePudding user response：

There is no INTERVAL MILLISECOND type in Presto/Trino, INTERVAL YEAR TO MONTH or INTERVAL DAY TO SECOND (docs, parse_duration(... || 'ms') will result in the second one)
AFAIK interval 'x' unit syntax works only with literals (i.e. you can't use columns here) and it seems does not support millisecond unit, so no help here

Is there a better/built-in method

Yes, the one you don't want to use - date_add

CodePudding user response：

The best method I could come up with, after some input from Guru, is:

date_add('millisecond', duration, TIME '00:00:00.000')