Round timestamp to quarter of hour

Question

I have a table and one of the columns is a timestamp. What I would like to do is a SQL query (BigQuery compatible) that rounds the timestamp of each line to the quarter of the hour previous to that time. Examples:

2019-07-05 21:11:28 UTC -> 2019-07-05 21:00:00 UTC 2019-07-05 21:17:05 UTC -> 2019-07-05 21:15:00 UTC 2019-07-05 20:29:56 UTC -> 2019-07-05 20:15:00 UTC 2019-07-05 21:55:39 UTC -> 2019-07-05 21:45:00 UTC

I found TIMESTAMP_TRUNC that can round to minutes, but this will round to the timestamp's minute, not the quarter.

Do you guys have any idea of how could I do this?

Thanks in advance

Answer 1

Below is for BigQuery Standard SQL

#standardSQL
SELECT ts, 
  TIMESTAMP_SECONDS(UNIX_SECONDS(ts) - MOD(UNIX_SECONDS(ts), 15 * 60)) ts_rounded_to_quarter_of_hour
FROM `project.dataset.table`

Another, slightly refactored version is

#standardSQL
SELECT ts, 
  TIMESTAMP_SECONDS(ts_seconds_since_epoch - MOD(ts_seconds_since_epoch, 15 * 60)) ts_rounded_to_quarter_of_hour
FROM `project.dataset.table`, UNNEST([UNIX_SECONDS(ts)]) ts_seconds_since_epoch

And finally, my favorite version would be

#standardSQL
CREATE TEMP FUNCTION TIMESTAMP_TRUNC_TO_QUATER_OF_HOUR(ts TIMESTAMP) AS ((
  SELECT TIMESTAMP_SECONDS(ts_seconds_since_epoch - MOD(ts_seconds_since_epoch, 15 * 60))
  FROM UNNEST([UNIX_SECONDS(ts)]) ts_seconds_since_epoch
));
SELECT ts, 
  TIMESTAMP_TRUNC_TO_QUATER_OF_HOUR(ts) AS ts_rounded_to_quarter_of_hour
FROM `project.dataset.table` 

You can test, play with above using sample data from your question as in below example

#standardSQL
CREATE TEMP FUNCTION TIMESTAMP_TRUNC_TO_QUATER_OF_HOUR(ts TIMESTAMP) AS ((
  SELECT TIMESTAMP_SECONDS(ts_seconds_since_epoch - MOD(ts_seconds_since_epoch, 15 * 60))
  FROM UNNEST([UNIX_SECONDS(ts)]) ts_seconds_since_epoch
));
WITH `project.dataset.table` AS (
  SELECT TIMESTAMP '2019-07-05 21:11:28 UTC' ts UNION ALL    --> 2019-07-05 21:00:00 UTC
  SELECT '2019-07-05 21:17:05 UTC' UNION ALL                 --> 2019-07-05 21:15:00 UTC
  SELECT '2019-07-05 20:29:56 UTC' UNION ALL                 --> 2019-07-05 20:15:00 UTC
  SELECT '2019-07-05 21:55:39 UTC'                           --> 2019-07-05 21:45:00 UTC
)
SELECT ts, 
  TIMESTAMP_TRUNC_TO_QUATER_OF_HOUR(ts) AS ts_rounded_to_quarter_of_hour
FROM `project.dataset.table`   

Obviously, all three above versions return below [same] result

Row     ts                      ts_rounded_to_quarter_of_hour    
1       2019-07-05 21:11:28     UTC 2019-07-05 21:00:00 UTC  
2       2019-07-05 21:17:05     UTC 2019-07-05 21:15:00 UTC  
3       2019-07-05 20:29:56     UTC 2019-07-05 20:15:00 UTC  
4       2019-07-05 21:55:39     UTC 2019-07-05 21:45:00 UTC