How to group by hour in Google Bigquery

My Google Bigquery table have date and time column. How to aggregate data for each day by one hour (24 records per day)?

Solution:

Below is for BigQuery Standard SQL

#standardSQL
SELECT date, TIME_TRUNC(time, HOUR) hour, SUM(col1) AS Col1, SUM(col2) AS Col2 
FROM `project.dataset.table`
GROUP BY date, hour 
ORDER BY date, hour

You can test, play with above using dummy data in your question:

#standardSQL
WITH `project.dataset.table` AS (
  SELECT DATE "2018-10-26" date, TIME "19:05:00" time, 1 col1, 2 col2 UNION ALL
  SELECT "2018-10-26", "19:15:00", 3, 4
) 
SELECT date, TIME_TRUNC(time, HOUR) hour, SUM(col1) AS Col1, SUM(col2) AS Col2 
FROM `project.dataset.table`
GROUP BY date, hour 
ORDER BY date, hour  

with result

Row date        hour        Col1    Col2     
1   2018-10-26  19:00:00    4       6    

Mike-Barn

posted on 09 Nov 18