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\nSELECT date, TIME_TRUNC(time, HOUR) hour, SUM(col1) AS Col1, SUM(col2) AS Col2 \nFROM `project.dataset.table`\nGROUP BY date, hour \nORDER BY date, hour\n

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

#standardSQL\nWITH `project.dataset.table` AS (\n  SELECT DATE "2018-10-26" date, TIME "19:05:00" time, 1 col1, 2 col2 UNION ALL\n  SELECT "2018-10-26", "19:15:00", 3, 4\n) \nSELECT date, TIME_TRUNC(time, HOUR) hour, SUM(col1) AS Col1, SUM(col2) AS Col2 \nFROM `project.dataset.table`\nGROUP BY date, hour \nORDER BY date, hour  \n

with result

Row date        hour        Col1    Col2     \n1   2018-10-26  19:00:00    4       6    \n

Mike-Barn

posted on