CODEWITHSUNDEEP
sqldatetimetimegoogle-bigqueryrounding
MARPLE NGUYEN
MARPLE NGUYEN

Reputation: 1

Is there any function extracting DD/MM/YY HH:MM:SS in BigQuery to hh:mm:ss?

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!

Upvotes: 0

Views: 46

Answers (1)

Samuel Kožuch
Samuel Kožuch

Reputation: 78

As others have pointed out, you should use TIMESTAMP_DIFF for this, to determine the difference between pickup_datetime and dropoff_datetime.

I'd recomment to determine the difference in seconds and then dividing by 60, as determining the difference in minutes would return only a whole number, eg 10 minutes, not 10.5 minutes for example.

See my query below:

SELECT
    pickup_location_id
  , dropoff_datetime
  , pickup_datetime
  , TIMESTAMP_DIFF(dropoff_datetime, pickup_datetime, SECOND) / 60 AS ride_in_minutes
FROM `bigquery-public-data.new_york_taxi_trips.tlc_yellow_trips_2022`

Upvotes: 0

Related Questions