Round to nearest MINUTE or HOUR in BigQuery

How to round to nearest MINUTE or Hour in Google Bigquery

BigQuery Standard SQL Syntax:

#standardSQL
WITH `project.dataset.table` AS (
  SELECT DATETIME '2018-01-01 01:05:56' input_datetime 
)
SELECT input_datetime,
  DATETIME_TRUNC(input_datetime, MINUTE) rounded_to_minute,
  DATETIME_TRUNC(input_datetime, HOUR) rounded_to_hour
FROM `project.dataset.table`   

Result:

Row     input_datetime          rounded_to_minute       rounded_to_hour  
1       2018-01-01T01:05:56     2018-01-01T01:05:00     2018-01-01T01:00:00  

For TIMESTAMP or TIME data types - you can use respectively - TIMESTAMP_TRUNC() or TIME_TRUNC()

Atori

posted on 15 Oct 18