CODEWITHSUNDEEP
pythontimezonegoogle-cloud-platformgoogle-bigquery
RSHAP
RSHAP

Reputation: 2447

BigQuery automatically converts timestamp timezone to UTC

I have a table as such:

enter image description here

and a file as such: https://storage.googleapis.com/test_share_file/testTimestamp.csv

which looks like: enter image description here

and I load the file to big query using python as such:

from google.cloud import bigquery as bq

gs_path = 'gs://test_share_file/testTimestamp.csv'
bq_client = bq.Client.from_service_account_json(gcp_creds_fp)
ds = bq_client.dataset('test1')
tbl = ds.table('testTimestamp')

job_config = bq.LoadJobConfig()
job_config.write_disposition = bq.job.WriteDisposition.WRITE_APPEND
job_config.skip_leading_rows = 1 # skip header
load_job = bq_client.load_table_from_uri(gs_path, tbl, job_config=job_config)
res = load_job.result()

and yet in the table, both timestamps are in UTC time!

enter image description here

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

Upvotes: 2

Views: 6723

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172994

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

#standardSQL
WITH t AS (
  SELECT TIMESTAMP '2018-05-07 22:40:00+00:00' AS ts
)
SELECT ts, STRING(ts, '-04:00') timestamp_eastern
FROM t

I am dealing with ... stubbornness ...

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 

#standardSQL
CREATE VIEW `project.dataset.your_view` AS 
SELECT ts, STRING(ts, '-04:00') timestamp_eastern 
FROM `project.dataset.your_table`

I do think it odd that big query can't display a time in a timezone

A timestamp represents an absolute point in time, independent of any time zone or convention such as Daylight Savings Time.
Time zones are used when parsing timestamps or formatting timestamps for display. The timestamp value itself does not store a specific time zone. A string-formatted timestamp may include a time zone. When a time zone is not explicitly specified, the default time zone, UTC, is used.

See more about Timestamp type

Upvotes: 2

Related Questions