15 October 2018 · Atori ·       Add to Favorites   Report

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 October 2018

Read great educational content like this and a lot more !

Members get free exclusive access to content, new courses, and discounts. Signup for a free account to write a post / comment / upvote posts. Creating an account takes less than 5 seconds and you can start earning badges & points too

Copied