Round Timstamp to nearest 15 mins interval in bigquery

user_8130742 0
Viewed 4574

I am trying to round the datetime field to nearest 15 mins interval through bigquery standard sql, tried datetime_trunc but it is not allowing to round off to nearest X mins

2018-10-24 01:05:00 to 2018-10-24 01:00:00
2018-10-24 01:08:00 to 2018-10-24 01:15:00
2018-10-24 01:12:00 to 2018-10-24 01:15:00

any other ways to achieve the above conversion in bq standard sql?

Thanks,

google-bigquery
1 Answers

Below is for BigQuery Standard SQL (assuming your field is of TIMESTAMP type as it is stated in question title) 

TIMESTAMP_SECONDS(900 * DIV(UNIX_SECONDS(dt_from) + 450, 900))

You can test, play with it using dummy data from your question 

#standardSQL
WITH `project.dataset.table` AS (
  SELECT TIMESTAMP '2018-10-24 01:05:00' dt_from UNION ALL
  SELECT '2018-10-24 01:08:00' UNION ALL
  SELECT '2018-10-24 01:12:00'
)
SELECT dt_from, TIMESTAMP_SECONDS(900 * DIV(UNIX_SECONDS(dt_from) + 450, 900)) dt_to
FROM `project.dataset.table`

with result 

Row dt_from                 dt_to    
1   2018-10-24 01:05:00 UTC 2018-10-24 01:00:00 UTC  
2   2018-10-24 01:08:00 UTC 2018-10-24 01:15:00 UTC  
3   2018-10-24 01:12:00 UTC 2018-10-24 01:15:00 UTC

In case if your field is of DATETIME type (as it stated in the question itself) - you can use below version of above 

#standardSQL
WITH `project.dataset.table` AS (
  SELECT DATETIME '2018-10-24 01:05:00' dt_from UNION ALL
  SELECT '2018-10-24 01:08:00' UNION ALL
  SELECT '2018-10-24 01:12:00' 
)
SELECT dt_from, DATETIME(TIMESTAMP_SECONDS(900 * DIV(UNIX_SECONDS(TIMESTAMP(dt_from)) + 450, 900))) dt_to
FROM `project.dataset.table`
user_52219440
Related