CODEWITHSUNDEEP
sqldatetimegoogle-bigquerytimezone-offset
Aleister Tanek Javas Mraz
Aleister Tanek Javas Mraz

Reputation: 517

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

Upvotes: 1

Views: 10283

Answers (3)

Arindam Karmakar
Arindam Karmakar

Reputation: 176

Just change the date-time format '%d/%m/%Y %H:%M:%S' and the zone 'Asia/Kolkata' below and run it. Find the example below for Indian Standard Time (IST).

BigQuery Command

select format_timestamp('%d/%m/%Y %H:%M:%S IST', datetime(timestamp_micros(event_timestamp), "Asia/Kolkata")) as time from table

Upvotes: 1

Daniel Zagales
Daniel Zagales

Reputation: 3032

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

Upvotes: 1

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173171

Try below

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

Upvotes: 1

Related Questions