How to group by hour in Google Bigquery

user_10564902 0
Viewed 15055

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);
}
....
google-bigquery google-apps-script google-sheets sql
2 Answers

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
user_52219440

You can use extract():

SELECT date, extract(hour from time) as hh,
       SUM(col1) AS Col1, SUM(col2) AS Col2
FROM ?
GROUP BY date, hh 
ORDER BY date, hh ASC;

Why would you be storing datetime values in two separate columns, rather than a single datetime or timestamp column?

user_11440350
Related