More

How to calculate average time when it is used TIME format in Bigquery?

I’m trying to get the AVG time, but the time format is not supported by the AVG function. I tried with CAST function, like in some posts were explained, but it seems doesn’t work anyway. Thanks

WITH october_fall AS
   (SELECT
   start_station_name,
   end_station_name,
   start_station_id,
   end_station_id,
   EXTRACT (DATE FROM started_at) AS start_date,
   EXTRACT(DAYOFWEEK FROM started_at) AS start_week_date,
   EXTRACT (TIME FROM started_at) AS start_time,    
   EXTRACT (DATE FROM ended_at) AS end_date,
   EXTRACT(DAYOFWEEK FROM ended_at) AS end_week_date,    
   EXTRACT (TIME FROM ended_at) AS end_time,
   DATETIME_DIFF (ended_at,started_at, MINUTE) AS total_lenght,
   member_casual
FROM 
   `ciclystic.cyclistic_seasonal_analysis.fall_202010` AS fall_analysis
ORDER BY 
   started_at DESC)
SELECT
   COUNT (start_week_date) AS avg_start_1,
   AVG (start_time) AS avg_start_time_1, ## here is where the problem start
   member_casual
FROM 
   october_fall
WHERE 
   start_week_date = 1
GROUP BY
   member_casual
Gooffe

Recent Posts

The Best Cat Bed for Your Furry Friend’s Perfect Nap!

Cats love to rest! You can frequently find your feline nestled into a radiant spot…

3 weeks ago

Eco-Friendly Diamonds: A Sustainable Choice for the Modern Era

The jewelry industry has been experiencing a shift in recent years as consumers grow increasingly…

4 weeks ago

The Allure of Moissanite Diamonds: A Far reaching Guide

Moissanite diamonds have rapidly gained popularity as an attractive alternative to traditional diamonds. Known for…

1 month ago

Parenting with Love and Logic: A Balanced Approach to Raising Responsible Children

Introduction Parenting is one of life’s most fulfilling yet demanding roles. As children grow, parents…

1 month ago

Simplicity Parenting: A Guide to Nurturing Children in a Complex World

In today’s fast-paced and consumer-driven society, many parents find themselves overwhelmed by the demands of…

1 month ago

Lowe’s Home Improvement – Las Vegas, NV

Las Vegas, Nevada, is a city known for its vibrant energy and dynamic living. Whether…

2 months ago