BigQuery - Convert Datetime to local timezone?-CodePudding

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)"

CodePudding user response：

Try below

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

CodePudding user response：

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