BigQuery converting to a different timezone

datetime timezone google-bigquery
akshah123 picture akshah123 · Sep 18, 2012 · Viewed 48.6k times · Source

I am storing data in unixtimestamp on google big query. However, when the user will ask for a report, she will need the filtering and grouping of data by her local timezone.

The data is stored in GMT. The user may wish to see the data in EST. The report may ask the data to be grouped by date.

I don't see the timezone conversion function here:

Does anyone know how I can do this in bigquery? i.e. how do i group by after converting the timestamp to a different timezone?

Answer

milyord picture milyord · Oct 19, 2016

As of September 2016 BigQuery has adopted standard SQL and you can now just use the "DATE(timestamp, timezone)" function to offset for a timezone. You can reference their docs here:

BigQuery DATE docs

Related questions

Convert UTC date time to local date time

From the server I get a datetime variable in this format: 6/29/2011 4:52:48 PM and it is in UTC time. I want to convert it to the current user’s browser time using JavaScript. How this can be done using JavaScript or …

Read More
Daylight saving time and time zone best practices

I am hoping to make this question and the answers to it the definitive guide to dealing with daylight saving time, in particular for dealing with the actual change overs. If you have anything to add, please do Many systems …

Read More
How to make an unaware datetime timezone aware in python

What I need to do I have a timezone-unaware datetime object, to which I need to add a time zone in order to be able to compare it with other timezone-aware datetime objects. I do not want to convert my …

Read More