Round to nearest MINUTE or HOUR in BigQuery

How to round to nearest MINUTE or Hour in Google Bigquery

BigQuery Standard SQL Syntax:

#standardSQL\nWITH `project.dataset.table` AS (\n  SELECT DATETIME '2018-01-01 01:05:56' input_datetime \n)\nSELECT input_datetime,\n  DATETIME_TRUNC(input_datetime, MINUTE) rounded_to_minute,\n  DATETIME_TRUNC(input_datetime, HOUR) rounded_to_hour\nFROM `project.dataset.table`   \n

Result:

Row     input_datetime          rounded_to_minute       rounded_to_hour  \n1       2018-01-01T01:05:56     2018-01-01T01:05:00     2018-01-01T01:00:00  \n

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

Atori

posted on