BigQuery converting to a different timezone

bigquery timezones
convert utc to timezone bigquery'
strftime bigquery
bigquery weeknum
big query parse datetime
bigquery datetime literal
bigquery group by day
bigquery create date

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?

2016 update: Look answers below, BigQuery now provides timestamp and timezone methods.


You are right - BigQuery doesn't provide any timestamp conversion methods.

In this case, I suggest that you run your GROUP BY based on dimensions of the GMT/UTC timestamp field, and then convert and display the result in the local timezone in your code.

BigQuery 🔎︄: Convert timestamp/date/datetime to different timezone , How to convert a timestamp/date/datetime to a different timezone in Google BigQuery. Posted by Pascal Landau on 2018-04-08 18:00:00. BigQuery provides​  BigQuery provides multiple functions to convert timestamps / dates / datetimes to a different timezone: According to the docu the timezone can be provided as UTC-offset (e.g. +02:00) or timezone name (e.g. Europe/Berlin). See this list of IANA timezone offsets and names.

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

Google BigQuery: How to convert a Date / Timestamp to a different , Google BigQuery: How to convert a Date / Timestamp to a different timezone. The date is stored in GMT. You may wish to see the data in EST or your local time. Google BigQuery: How to convert a Date / Timestamp to a different timezone. The date is stored in GMT. You may wish to see the data in EST or your local time.

Standard SQL in BigQuery has built-in functions:

DATE(timestamp_expression, timezone)
TIME(timestamp, timezone)
DATETIME(timestamp_expression, timezone)

Example:

SELECT 
   original,
   DATETIME(original, "America/Los_Angeles") as adjusted
FROM sometable;

+---------------------+---------------------+
| original            | adjusted            |
+---------------------+---------------------+
| 2008-12-25 05:30:00 | 2008-12-24 21:30:00 |
+---------------------+---------------------+

You can use standard IANA timezone names or offsets.

Timestamp Functions in Standard SQL | BigQuery, https://cloud.google.com › › Cloud Dataprep by Trifacta › Documentation 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. Recommend:datetime - Converting ISO 8601 date format with timezone in python. ect in python.

Your premise is right. If you group like this, then users who want EST or EDT will get incorrect date grouping:

GROUP BY UTC_USEC_TO_DAY(ts_field)

But as long as you figure out the offset that your user wants, you can still do the full calculation on the server. For example, if EST is 5 hours behind UTC then query like this:

GROUP BY UTC_USEC_TO_DAY(ts_field - (5*60*60*1000*1000000) )

Just parameterize the "5" to be the offset in hours, and you're all set. Here's a sample based on one of the sample data sets:

SELECT
  COUNT(*) as the_count,
  UTC_USEC_TO_DAY(timestamp * 1000000 - (5*60*60*1000*1000000) ) as the_day
FROM
  [publicdata:samples.wikipedia]
WHERE
  comment CONTAINS 'disaster'
  and timestamp >= 1104537600
GROUP BY
  the_day
ORDER BY
  the_day

You can remove the offset to see how some edits move to different days.

Supported Time Zone Values, The following American time zones are mapped to the time zone values New Hampshire, New Jersey, New York, North Carolina, Ohio, Pennsylvania, Rhode  Use this parameter if you want to use a time zone other than the default time zone, UTC, as part of the truncate operation. When truncating a TIMESTAMP to MINUTE or HOUR , TIMESTAMP_TRUNC determines the civil time of the TIMESTAMP in the specified (or default) time zone and subtracts the minutes and seconds (when truncating to HOUR) or the seconds (when truncating to MINUTE) from that TIMESTAMP .

To convert any TimeZone DateTime string to UTC, one could use PARSE_TIMESTAMP using the supported TIMESTAMP Formats in BigQuery.

For example to convert IST (Indian Standard Time) string to UTC, use the following:

SAFE.PARSE_TIMESTAMP("%a %b %d %T IST %Y", timeStamp_vendor, "Asia/Kolkata")

Here PARSE_TIMESTAMP parses the IST string to a UTC TIMESTAMP (not string). Adding SAFE as prefix takes care of errors/nulls etc.

To convert this to a readable string format in BigQuery, use FORMAT_TIMESTAMP as follows:

FORMAT_TIMESTAMP("%d-%b-%Y %T %Z", SAFE.PARSE_TIMESTAMP("%a %b %d %T IST %Y", timeStamp_vendor, "Asia/Kolkata"))

This example would take an IST string of the format Fri May 12 09:45:12 IST 2019 and convert it to 12-May-2019 04:15:12 UTC.

Replace IST with the required TimeZone and Asia/Kolkata with relevant Timezone name to achieve the conversion for your timezone

Convert timestamp/date/datetime to different timezone in BigQuery , convert-timestamp-to-different-timezone.sql. #standardSQL. # Convert date / time to a different timezone in BigQuery; standard-sql; 2018-04-08. You can try "GROUP BY hour", convert into days according to timezone client-side. But then you have ~24x as much data transferred, plus it doesn't work for :30 (and :15) timezones. If you want to cover that, you need to handle even more crap.

Formatting and dealing with dates in different SQL dialects, It turns out migrating from Redshift to Bigquery was not “as easy as Formatting; Time differences / deltas; Intervals; Time zones; Current times Here's how these different conversion functions look across major SQL dialects  BigQuery coerces the result type of an expression to another type if needed to match function signatures. For example, if function func() is defined to take a single argument of type INT64 and an expression is used as an argument that has a result type of FLOAT64, then the result of the expression will be coerced to INT64 type before func() is computed.

Create view from table does not consider timezone for datetime , Consider the following Bigquery View: SELECT CURRENT_DATETIME as dt It Would this not be rectified by the timezone conversion that would run when the The issue I'm bringing is different - it's about DATETIME fields, being possibly  Time Zone Converter – Time Difference Calculator Provides time zone conversions taking into account Daylight Saving Time (DST), local time zone and accepts present, past, or future dates. Start by adding a city to convert from:

Is it possible to change a datetime to a different time zone?, In BigQuery I can do something like DATETIME(mydatetime,'Europe/London') to get this result, but I was hoping to do it all in Dataprep if that's  BigQuery converting to a different timezone (3) 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.

Comments
  • Hi Michael, Thanks for the response. the problem is that suppose I need data grouped by date. Now, the data needs to be grouped by the date in local timezone. i.e. if its 1 am today in GMT, its actually yesterday in EST. I would have to group data by each timestamp value and return it. However, this will require a lot of network IO and a very slow performance on overall report. Is there any hacks I can do as a work around?
  • Why not have a column that records the user's local timezone with each record? Then you can quickly iterate through the resulting rows and provide the proper display date.
  • we did consider this option but the problem is that multiple users may request to see the same data. So, it is not practical so store the value in every possible conversion with the record. However, for now, we only have users in EST so we can choose this route. This would be a problem once we have users outside of the region.
  • By the way, is there somewhere I can suggest this feature to be added in big query?
  • UPDATE: we have started a new Google BigQuery public issue tracker to help surface feature requests and bug reports: code.google.com/p/google-bigquery
  • It is true that this functionality is new, but it does not solve the problem. If you have a timestamp, you typically want a new timestamp. This function takes in a timestamp and outputs a date.
  • @DennisJaheruddin A timestamp is an absolute point in time (microseconds from the Unix epoch for BigQuery). There is no "new" timestamp to get. If you want that point in time represented in a specific time zone then you have to convert that absolute time into a logical calendar time, usually represented by date. This is exactly what this function does.
  • @ManiGandham I don't understand your response, what I am talking about is that this command changes something like 1-jan-2001 07:00:00 (don't mind the format) into 1 jan 2001, so it is only the date without the time.
  • @DennisJaheruddin Did you read the documentation? You can use DATETIME(timestamp, timezone) instead if you need a DateTime result. cloud.google.com/bigquery/docs/reference/standard-sql/…
  • In that case I either missed it or the docs have been updated since I checked in February. Either way, it is nice that you posted this answer.
  • Hi, thanks for the feedback. this would def work for straightforward conversions. However, the daylight savings time throw a wrench in the problem as you can't just use a static value.
  • Agreed: you need your app (or your users!) to figure out the offset they want. That's not really an ideal solution. You'll definitely miss out on edge cases like what are the exact boundaries of "today" on the day Daylight Saving Time starts or ends. But it would cover many needs.
  • we are following the same approach.