Convert to date to time zone in legacy BigQuery

1
votes

In new BigQuery SQL (standard) you can use DATE(some_time_stamp, "Asia/Jerusalem") in order to convert UTC to local time zone. I couldn't find such a function in legacy SQL for BigQuery.

How do I convert UTC timestamp to time zone time using legacy SQL in BigQuery?

1
sqlgoogle-bigquery
just wondering: any reason that stops you from migration to standard? – Felipe Hoffa
@FelipeHoffa I want to create a view to be used by Google Data Studio - but data studio can not read views written in standard (weird!). – MeLight
what if you create a custom query in Data Studio? #standardSQL SELECT * FROM view – Felipe Hoffa
@FelipeHoffa I'll tell our data guy to try that. – MeLight

1 Answers

2
votes

There is no timezone function within legacy, only standard. You would have to implement your own time conversion logic e.g.

SELECT   DATE_ADD(CURRENT_TIMESTAMP(), 300, "MINUTE") PlusFiveHours