如何在BigQuery中将时间戳的毫秒到秒四舍五入

Question

我正在尝试从时间戳中删除毫秒，并在这样做的过程中对值进行四舍五入。

From this: 2019-11-11 19:10:57.981 UTC
To this: 2019-11-11 19:10:58 UTC```
Yes, I want the second rounded when truncated. 

Answer 1

下面是BigQuery标准SQL

#standardSQL
SELECT TIMESTAMP_TRUNC(TIMESTAMP_ADD(ingest_time, INTERVAL 500 MILLISECOND), SECOND)
FROM `project.dataset.table`

你可以测试，玩上面使用伪数据，如下面的例子

#standardSQL
WITH `project.dataset.table` AS (
SELECT TIMESTAMP '2019-11-11 19:10:57.981 UTC' ingest_time UNION ALL
SELECT '2019-11-11 19:10:57.381 UTC'
)
SELECT TIMESTAMP_TRUNC(TIMESTAMP_ADD(ingest_time, INTERVAL 500 MILLISECOND), SECOND)
FROM `project.dataset.table`   

结果

Row f0_  
1   2019-11-11 19:10:58 UTC  
2   2019-11-11 19:10:57 UTC