CODEWITHSUNDEEP
google-bigquerytimestamp-with-timezone
Saminator
Saminator

Reputation: 77

BigQuery Google timezone conversion

When I try running below query I get timestamp in UTC timezone.

select current_timestamp from table;

Can you please help me to convert timestamp to get in EST timezone.

Thanks

Upvotes: 2

Views: 9922

Answers (3)

David Scott Kirby
David Scott Kirby

Reputation: 306

For more control you can use FORMAT_TIMESTAMP (https://cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions#format_timestamp)

Example:

SELECT FORMAT_TIMESTAMP("%F %T EST", current_timestamp, "America/New_York") AS current_timestamp_EST FROM `dataset.table` LIMIT 1;

Results:

|-------------------------|
|  current_timestamp_EST  |
|-------------------------|
| 2020-10-23 18:25:17 EST |
|-------------------------|

Upvotes: 0

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

SELECT TIMESTAMP(DATETIME(CURRENT_TIMESTAMP), 'America/New_York')

The trick here is in converting TIMESTAMP to DATETIME which is timezone-less to represent timestamp as a just date/time, then convert back to TIMESTAMP but now specifying needed timezone.
Note, BigQuery still will show it as UTC but timestamp value itself will represent value in respective timezone

Upvotes: 3

Elliott Brossard
Elliott Brossard

Reputation: 33745

Try this instead:

SELECT STRING(CURRENT_TIMESTAMP, 'America/New_York') AS current_timestamp
FROM dataset.table

This converts the timestamps to strings using the New York time zone.

Upvotes: 0

Related Questions