Convert Timestamp from UTC to PST

Google Cloud Data Analytics
, , , , , , ,
1

Hi Everyone,

I am using data fusion to extract data which is loaded into BigQuery. From the source table, the datefield is stored as “TIMESTAMP_NTZ(9)” and currently its loading as a UTC format in BigQuery. I am trying to figure out, which syntax can be used in wrangler which converts the fields to PST.

Sample of date stored in BigQuery: “2020-10-30T10:15:12Z[UTC]”

At the moment, I have tried a few variety of set-column, parse-as-simple-date, timestamp-to-datetime but not successful at the moment.

I would appreciate any guidance if this conversion is possible.

Thanks in advance!

RJ

2

Hey RJ, will the below help you out?

TIMESTAMP(DATETIME(CURRENT_TIMESTAMP(), “America/Los_Angeles”))

1 Like
3

If you are asking how to store the timestamp in PST, you can’t. BigQuery stores all TIMESTAMP values in UTC internally.

If you want to load the timestamp data as is, use the datetime data type for your datefield in your destination table instead of timestamp.

4

Hi @RajneelRam ,

Welcome to Google Cloud Community!

You need to specify your time zone when loading your data to BigQuery. If there is no time zone specified, it will use the default time zone which is UTC. Check again your directives and validate if you specify your time zone.

If the issue persists, you can apply the transformation in BigQuery using the SQL provided by @Rhountu .

SELECT
 Timestamp_NTZ AS original_timestamp,
 TIMESTAMP(DATETIME(Timestamp_NTZ, 'America/Los_Angeles')) AS pst
FROM `project_name.dataset_name.table_name`

Was this helpful? If so, please accept this answer as “Solution”. If you need additional assistance, reply here within 2 business days and I’ll be happy to help.

5

Thank you for sharing this information.

6

Hi Rhountu

Thanks for helping out. Unfortunately when I try this syntax, it doesn’t work in snowflake. I am getting a error when running this.

7

@caryna

Thanks for helping out. Unfortunately when I try this syntax, it didn’t work. I am getting a error when running this. attached screenshot of error.

SQL-Error.JPG
SQL-Error.JPG1524×103 45.3 KB

8

@RajneelRam

You might also find the example here helpful: Timezone in BigQuery

9

If this is a Snowflake query, you might get more appropriate answers in the Snowflake Community Forums

10

Thank you @mars124 let me try this route. Will provide some feedback tomorrow.