So I am using Google Big Query, and I want to find the average time for my entire column. The time is formatted as hh:mm:ss for the entire column, for example 00:19:00.

select 
 AVG(ride_length) AS average_duration
FROM `casestudy1-361603.project.DecData`

However there is an error saying, "No matching signature for aggregate function AVG for argument types: STRING".

This is how the column looks

CodePudding user response：

Cast strings to interval datatype and perform an average:

select avg(cast(t as interval)) as agg
from unnest(['10:20:30', '30:40:50']) as t

returns

UPD: If you want to extract specific parts of the result, you may use justify_interval to normalize interval to standard day duration (24h) and use extract function to extract portions.

with src as (
  select avg(cast(t as interval)) as agg
  from unnest(['70:20:30', '40:40:50']) as t
)
select
  src.agg,
  justify_interval(agg) as normalized_,
  extract(hour from justify_interval(agg)) as h
from src