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>>';