'BigQuery - Convert Datetime to local timezone?

I have a DateTime column with data like "2020-08-05T10:19:30" in UTC.

I need to convert this to either "America/Los Angeles" or "America/Chicago" timezone with a BigQuery SQL call.

I've tried:

SELECT A, B, C, DATETIME(date_field,"America/Los Angeles") as new_date_field FROM table;

I get this:

"No matching signature for function DATETIME for argument types: DATETIME, STRING. Supported signatures: DATETIME(INT64, INT64, INT64, INT64, INT64, INT64); DATETIME(DATE, TIME); DATETIME(TIMESTAMP, [STRING]); DATETIME(DATE); DATETIME(DATETIME); DATETIME(STRING)"

sqldatetimegoogle-bigquerytimezone-offset


Solution 1:[1]

You're seeing the error because your data is already in a datetime data type and that is not a valid data type for the datetime function. Instead try the following:

with sample_data as (
select  DATETIME("2020-08-05T10:19:30") as date_field
)
select  datetime(timestamp(date_field), "America/Los_Angeles") from sample_data

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Daniel Zagales

Related Questions