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

Loose Lab Grown Diamonds: A Sustainable and Affordable Choice for Stunning Jewelry

Lab grown diamonds have revolutionized the jewelry industry, offering an ethical and cost-effective alternative to…

4 weeks ago

Discover the Excellence of Lab-Created Diamonds Necklaces

Welcome to our comprehensive guide on lab-created diamonds necklaces, where elegance meets innovation in the…

4 weeks ago

Cano Health: Revolutionizing Healthcare for You

Introduction to Cano Health Welcome to Cano Health – your trusted partner in wellness. In…

1 month ago

The Role of Therapy Dogs in Reducing Anxiety and Depression

While individuals engage in modern life activities, fear and sadness appear as allies, so people…

1 month ago

Sustainability in Sparkle: Environmental Advantages of Lab Grown Diamonds

Mined diamonds have long been hailed as the pinnacle of rarity and luxury, coveted for…

1 month ago

Bulk Purchases: Wholesale Lab Created Diamonds for Jewelers

In the dynamic world of jewelry and gemstones, the emergence of lab-created wholesale lab created…

1 month ago