TIMESTAMP literals in FOR SYSTEM_TIME AS OF don't default to UTC

1k views Asked by At

Google BigQuery documentation says...

A timestamp literal can include a numerical suffix to indicate the time zone:

TIMESTAMP '2014-09-27 12:30:00.45-08'

If this suffix is absent, the default time zone, UTC, is used.

This is also what I observe except in context of the FOR SYSTEM_TIME AS OF clause.

E.g. if I run this query:

SELECT * FROM doesnt.matter FOR SYSTEM_TIME AS OF TIMESTAMP'2020-12-16 17:59:14.445596'

I get the following error message:

'FOR SYSTEM_TIME AS OF' expression for table 'doesntmatter' evaluates to a TIMESTAMP value in the future: 2020-12-17 01:59:14.445596 UTC.

The interesting part is the timestamp in the error message: It says UTC but is clearly different from the one in my query (which should be interpreted as UTC according to the docs above).

In fact I get the very same error message if I add a "-08" as a timezone to the timestamp literal. It seems that this 8 hour offset isn't affected by the dataset location (I get the same error whether I use US or EU).

If I add an explicit UTC timezone to the timestamp, the error message makes sense:

SELECT * FROM doesnt.matter FOR SYSTEM_TIME AS OF TIMESTAMP'2020-12-16 17:59:14.445596 UTC'

Not found: Dataset [...]:doesnt was not found in location US

(Neither dataset nor table existed at any time).

Am I doing something wrong (am I missing something in the docs) or is this a BigQuery issue?


Workaround

In the ticket, Google also presents this workaround:

SET @@time_zone = 'UTC';

For clarity, the same problem persists when actually creating a table (based on the example given in the docs):

-- Create table books.
CREATE OR REPLACE TABLE test.books AS
SELECT 'Hamlet' title, 'William Shakespeare' author;

-- Get current timestamp before table replacement.
SELECT current_timestamp;

-- Replace table with different schema(title and release_date).
CREATE OR REPLACE TABLE test.books AS
SELECT 'Hamlet' title, DATE '1603-01-01' release_date;

-- Manually copy and paste the timestamp from line the SELECT current_timestamp query without timezone:
SELECT * FROM doesnt.matter FOR SYSTEM_TIME AS OF TIMESTAMP'<<paste here>>';
0

There are 0 answers