BigQuery automatically converts timestamp timezone to UTC

Here's my source data:

Once i load the data into my bigquery table, it automatically converts the timestamp to UTC:

How do I get the second column to be in eastern time?

Solution:

You can "transform" first column into eastern time on-fly - something like in below example

\n\n
#standardSQL\nWITH t AS (\n  SELECT TIMESTAMP '2018-05-07 22:40:00+00:00' AS ts\n)\nSELECT ts, STRING(ts, '-04:00') timestamp_eastern\nFROM t
\n\n

You can create view which will consists of all the logic you need in place so client will query that view instead of original table

\n\n
#standardSQL\nCREATE VIEW `project.dataset.your_view` AS \nSELECT ts, STRING(ts, '-04:00') timestamp_eastern \nFROM `project.dataset.your_table`

victor

posted on

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



 Comments are Locked for this post