2

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".

ANES
  • 21
  • 1
  • date/time arithmetic is dependent on the database product being used (Oracle, SqlServer, PostgreSQL, MySql, etc); tag the product you use. – tinazmu Mar 15 '22 at 12:41
  • Welcome to Stack Overflow! Each make and version of database server has its own dialect of SQL. Please [edit] your question to add a [tag](//stackoverflow.com/help/tagging) for the database you use. [tag:postgresql]? [tag:mysql]? [tag:sql-server]? [tag:oracle]? [tag:google-bigquery]? [tag:amazon-redshift]? Another? – O. Jones Mar 17 '22 at 11:14
  • Intervals can represent a large range of values, from microseconds out to centuries. How is the system meant to know *at what granularity* you care about when you cast it to an integer? Look in your documentation for a suitable function to extract values at your required level from the Interval. – Damien_The_Unbeliever Mar 17 '22 at 11:21

1 Answers1

1

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
Mikhail Berlyant
  • 165,386
  • 8
  • 154
  • 230