CODEWITHSUNDEEP
google-bigquery
ryantuck
ryantuck

Reputation: 6634

Elegant timezone conversion in BigQuery

I've loaded data into BigQuery with eastern timestamps that are stored as naive timestamps from the source data:

placed_eastern
-------------------
2018-07-23 00:00:03

BigQuery by default assumes naive timestamps should be stored as UTC:

2018-07-23 00:00:03 UTC

Problem: I need to fix this timezone assumption.

I currently have an ugly hack in place for fixing this error to ensure that the timestamps are correctly stored. This extracts the date and time parts of the timestamp out as strings, concatenates them, then recreates a timestamp.

select 
  placed_eastern,
  timestamp(
    concat(
      cast(extract(date from placed_eastern) as string), 
      ' ', 
      cast(extract(time from placed_eastern) as string)
    ),
    'US/Eastern'
  ) as actual_placed_utc

Question: Is there an elegant documented way to handle this problem?

Upvotes: 1

Views: 234

Answers (1)

Elliott Brossard
Elliott Brossard

Reputation: 33705

You can convert to DATETIME to treat the timestamp as a logical date/time, then convert back to TIMESTAMP using US/Eastern:

SELECT TIMESTAMP(DATETIME(placed_eastern), 'US/Eastern')) AS actual_placed_utc
FROM dataset.table

Upvotes: 4

Related Questions