sql - 将时间戳舍入到一刻钟

Question

我有一张表，其中一列是时间戳。我想做的是一个 SQL 查询(与 BigQuery 兼容)，它将每行的时间戳四舍五入到该时间之前的四分之一小时。示例:

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

我发现 TIMESTAMP_TRUNC 可以四舍五入到分钟，但这将四舍五入到时间戳的分钟，而不是四分之一。

你们知道我该怎么做吗？

提前致谢

Answer 1

以下是 BigQuery 标准 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`

另一个稍微重构的版本是

#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

最后，我最喜欢的版本是

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

您可以使用问题中的样本数据进行测试，如以下示例所示

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

显然，上述所有三个版本都返回以下 [相同] 结果

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