Search code examples
prestotrino

Convert Timestamps to Minute to Timestamp format

I have a dataset below: trip_duration is the final_date_time - date_time divided by 60 to get the difference in minutes.

date_time final_date_time trip_duration
2023-03-31 16:06:40 2023-03-31 16:21:53 0 00:00:15.216
2023-03-31 16:06:36 2023-03-31 16:25:00 0 00:00:18.400

I want my results to look like below:

date_time final_date_time trip_duration
2023-04-14 16:40:24 2023-04-14 16:49:05 0:00:08
2023-04-16 12:06:22 2023-04-16 12:15:18 0:00:08

I want to strip the last 4 characters and the first two characters.

I currently am doing this in Python but am wondering if there was a way to do this in Trino while keeping it as a timestamp

I've tried the below:

  1. CAST the trip_duration to a varchar Use substring(trip_duration,3,8) to trim to what I want.

I tried to CAST(SUBSTRING('0 00:00:18.400', 3, 8) as timestamp) however, it says value cannot be cast to timestamp. Is this possible at all?

Solution

  • It's a bit messy, but I think this would give what you're looking for:

    parse_duration(CAST(date_diff('minute', date_time, final_date_time) AS VARCHAR) || 'm')

    00:00:00.000 format is duration not a timestamp.