02 Aug 18 · npack · #bigquery ·   Bookmark   ×

Google BigQuery: How to convert a Date / Timestamp to a different timezone

The date is stored in GMT. You may wish to see the data in EST or your local time. How to convert the timestamp to a different timezone?

Solution: 

Use the cast function

TIMESTAMP(datetime_expression, timezone)
DATE(timestamp_expression,timezone)

Examples:

SELECT
CAST(TIMESTAMP("2008-12-25 15:30:00", "America/Los_Angeles") AS STRING) AS timestamp_str,
CAST(TIMESTAMP(DATE "2008-12-25", "America/Los_Angeles") AS STRING) AS timestamp_date,
CAST(TIMESTAMP(DATETIME "2008-12-25 15:30:00", "America/Los_Angeles") AS STRING) AS timestamp_datetime;
+------------------------+------------------------+------------------------+
| timestamp_str | timestamp_date | timestamp_datetime |
+------------------------+------------------------+------------------------+
| 2008-12-25 23:30:00+00 | 2008-12-25 08:00:00+00 | 2008-12-25 23:30:00+00 |
+------------------------+------------------------+------------------------+
SELECT
DATE(2016, 12, 25) as date_ymd,
DATE(TIMESTAMP "2016-12-25 05:30:00+07", "America/Los_Angeles") as date_tstz;
+------------+------------+
| date_ymd | date_tstz |
+------------+------------+
| 2016-12-25 | 2016-12-24 |
+------------+------------+

npack

posted on 02 Aug 18

Enjoy great content like this and a lot more !

Signup for a free account to write a post / comment / upvote posts. Its simple and takes less than 5 seconds

Copied