How to get current TIMESTAMP in UTC from BigQuery?

Question

I want to add to my query TIMESTAMP column in UTC.

I know BigQuery has CURRENT_TIMESTAMP() function but it doesn't say how to convert it to UTC.

In MYSQL I would have do:

SELECT UTC_TIMESTAMP()

What is the equivalent in BigQuery (Standard SQL only) ?

Answer 1

A TIMESTAMP does not store or use a time zone, as described in the documentation. When parsing it it may have a time zone which is converted from when saving.

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

And more specifically

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.

When querying for CURRENT_TIMESTAMP() it also shows explicitly it’s in UTC by having zero time zone offset. So it is a timestamp in UTC based on this.

There may be a problem using it since it does not store anything in UTC and it does not care about leap seconds. The specification says this:

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.

So if these leap seconds are needed it may be required to use another data type in specific time zone to handle leap seconds.

The conversion is also explained:

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.