Convert time to integer with CAST in SQL

user_18471937 0
Viewed 145

I want to have the average trip_duration for values that are over 0, so i thought to cast time as int64 TO be able to campare it to 0 in the where clause, but it didn't really work.

(SELECT (ended_at-started_at) as trip_duration

FROM `case-study-bikes-trips.bikes_trips.03_2021` )
select CAST (avg(trip_duration) as INT64)
from duration

It tells me "Invalid cast from INTERVAL to INT64".

google-bigquery int64 time casting sql
1 Answers

Consider below fix:

with duration as (
  select datetime_diff(ended_at, started_at, minute) as trip_duration
  from `case-study-bikes-trips.bikes_trips.03_2021` 
)
select avg(trip_duration) as avg_trip_duration_in_min
from duration
user_52219440
Related