In the bigquery-public-data.new_york_taxi_trips.tlc_yellow_trips_2022, the dropoff_datetime and pickup_datetime are in DD/MM/YY HH:MM:SS format, and I want to find pickup_location_id and subquery.avg_duration as the following:

SELECT
pickup_location_id,
subquery.avg_duration
FROM
(
SELECT pickup_location_id,
AVG(TIME(dropoff_datetime) – TIME (pickup_datetime)) AS avg_duration
FROM bigquery-public-data.new_york_taxi_trips.tlc_yellow_trips_2022
GROUP BY
pickup_location_id) AS subquery
ORDER BY
avg_duration DESC

Finally, the result is like in the picture. Is there any method that I can convert the avg_duration into hh:mm:ss and can use the ROUND function?

For example, avg_duration = 00:23:18 or 23:18 (because there are a few values including hour, such as 1:23:18) but not 0:23:18.4113 or 0-0 0 1:24:41.352833

Note: I have tested EXTRACT, ROUND, TIMESTAMP_DIFF, TIME_DIFF, to_char and TIME but it doesn’t work. They are unable to run in BigQuery. Thank you!