How to group by hour in Google Bigquery

Question

My Google Bigquery table have date and time column. New data are posted in table every 10 minutes, so date field would have e.g. "2018-10-26" and time field "19:05:00". Next record would be like "2018-10-26" and "19:15:00" for date and time field. How to aggregate data for each day by one hour (24 records per day)?

SQL request is sent from Google Sheets using Apps Script. Here is part of google bigquery.gs script: (complete script in GitHub)

...
var sheet = SpreadsheetApp.getActiveSheet();

var sql = 'SELECT  date, time, SUM(col1) AS Col1, SUM(col2) AS Col2 GROUP BY 
time, date ORDER BY time ASC';

var queryResults; 

// Inserts a Query Job
try {
var queryRequest = BigQuery.newQueryRequest();
queryRequest.setQuery(sql).setTimeoutMs(100000);
queryResults = BigQuery.Jobs.query(queryRequest, projectNumber);
}
....

Answer 1

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