Round to nearest MINUTE or HOUR in Standard SQL BigQuery

Question

There are some easy short ways to round to nearest MINUTE for T-SQL as noted here.

I am looking to get the same short syntax for Standard SQL.

Which type are you working with? `DATETIME`? `TIMESTAMP`? `TIME`? — Elliott Brossard, Jul 10 '18 at 14:19

score 15 · Accepted Answer · answered Jul 10 '18 at 18:35

Below is for BigQuery Standard SQL

#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`

with result as

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()

score 9 · Answer 2 · answered Apr 21 '20 at 18:39

There's some ambiguity in the question. The use of "round" in this context can be interpreted as "truncate to the nearest hour or minute," which the patterns so far demonstrate, and which probably has a closer analogy in floor than it does round. However, there are legitimate cases where one might want to truly "round" a timestamp to the nearest hour—that is, emulate a round operation. This is certainly doable:

with events as
(
  select timestamp('2020-04-21T18:21:55.967+00:00') as event_at
  union all
  select timestamp('2020-04-21T18:51:55.967+00:00') as event_at
)
select event_at
  , timestamp_trunc(event_at, hour) as event_truncated_hour
  , timestamp_trunc(timestamp_add(event_at, interval 30 minute), hour) as event_rounded_hour
from events

which yields

| event_at                         | event_truncated_hour           | event_rounded_hour            |
|----------------------------------|--------------------------------|-------------------------------|
| 2020-04-21T18:21:55.967+00:00    | 2020-04-21T18:00:00.000+00:00  | 2020-04-21T18:00:00.000+00:00 |
| 2020-04-21T18:51:55.967+00:00    | 2020-04-21T18:00:00.000+00:00  | 2020-04-21T19:00:00.000+00:00 |

score 1 · Answer 3 · answered Jul 10 '18 at 14:25

The same Standard SQL syntax is available in BQ as well

Input as datetime 2018-01-01 01:05:56

select 
    DATETIME_ADD(datetime '1800-01-01 00:00:00',
    INTERVAL DATETIME_DIFF(datetime '2018-01-01 01:05:56','1800-01-01 00:00:00',MINUTE) MINUTE) as rounded_to_minute,

    DATETIME_ADD(datetime '2010-01-01 00:00:00',
    INTERVAL DATETIME_DIFF(datetime '2018-01-01 01:05:56','2010-01-01 00:00:00',HOUR) HOUR) as rounded_to_hour

which produces:

+-----+---------------------+---------------------+--+
| Row |  rounded_to_minute  |   rounded_to_hour   |  |
+-----+---------------------+---------------------+--+
|   1 | 2018-01-01T01:05:00 | 2018-01-01T01:00:00 |  |
+-----+---------------------+---------------------+--+

score 0 · Answer 4 · answered Jul 19 '19 at 03:22

If you have a timestamp then you can use TIMESTAMP_TRUNC:

Google documentation

This also allow you to truncate by a specified timezone (so, for example you can specify the start of a day for a specified timezone. For example to truncate to the beginning of a day in Victoria, Australia:

TIMESTAMP_TRUNC(sample_date_time, DAY, 'Australia/Victoria') as sample_date_time

You can even use aggregates as the value to truncate e.g.

TIMESTAMP_TRUNC(min(sample_date_time), DAY, 'Australia/Victoria') as sample_date_time,

Round to nearest MINUTE or HOUR in Standard SQL BigQuery

4 Answers4