Round Timstamp to nearest 15 mins interval in bigquery

Question

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,

Answer 1

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`