CODEWITHSUNDEEP
sqlgoogle-apps-scriptgoogle-sheetsgoogle-bigquery
Intars_K
Intars_K

Reputation: 95

How to group by hour in Google Bigquery

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);
}
....

Upvotes: 9

Views: 22675

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172974

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

Upvotes: 14

Gordon Linoff
Gordon Linoff

Reputation: 1269563

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?

Upvotes: 2

Related Questions