sql - 如何从 BigQuery 获取 UTC 中的当前时间戳？

Question

我想添加到我的查询 TIMESTAMP栏目UTC .

我知道 BigQuery 有 CURRENT_TIMESTAMP()函数，但没有说明如何将其转换为 UTC .

在 MYSQL我会这样做:

SELECT UTC_TIMESTAMP()

BigQuery 中的等效项(仅限标准 SQL)是什么？

Answer 1

一个 TIMESTAMP不存储或使用时区，如 documentation 中所述.解析它时，它可能有一个时区，该时区从保存时转换而来。

A timestamp represents an absolute point in time, independent of any time zone or convention such as Daylight Savings Time.

更具体地说

A timestamp is simply an offset from 1970-01-01 00:00:00 UTC, assuming there are exactly 60 seconds per minute. Leap seconds are not represented as part of a stored timestamp.

查询 CURRENT_TIMESTAMP() 时它还通过零时区偏移明确显示它在 UTC 中。因此，它是基于此的 UTC 时间戳。

使用它可能会出现问题，因为它不以 UTC 格式存储任何内容，也不关心闰秒。规范是这样说的:

Leap seconds are only observable through functions that measure real-world time. In these functions, it is possible for a timestamp second to be skipped or repeated when there is a leap second.

因此，如果需要这些闰秒，则可能需要在特定时区使用另一种数据类型来处理闰秒。

还解释了转换:

If your input contains values that use ":60" in the seconds field to represent a leap second, that leap second is not preserved when converting to a timestamp value. Instead that value is interpreted as a timestamp with ":00" in the seconds field of the following minute.