Provide timezone conversion functions in bigquery

[GoogleCodeExporter]

At present, bigquery does not have any function that can provide time zone c 
onversions.

This issue is discussed in more detail here: 
http://stackoverflow.com/questions/12482637/bigquery-converting-to-a-different-t
imezone/12482905#comment20030832_12482905

As a workaround one can use static conversion by using something like this: 
UTC_USEC_TO_DAY(timestamp * 1000000 - (5*60*60*1000*1000000) ) but it doesn't 
work for daylight savings time.

It would be great if this functionality was provided as the date could be 
stored in GMT but the users may wish to see the report in any given timezone.

Original issue reported on code.google.com by an...@yhmg.com on 21 Feb 2013 at 3:40

[GoogleCodeExporter]

Original comment by manoochehri@google.com on 8 Mar 2013 at 11:49

• Changed state: Accepted

[GoogleCodeExporter]

Any progress on this issue ?

Original comment by MGe...@gmail.com on 21 Jan 2014 at 4:40

[GoogleCodeExporter]

We do not have near-term plans to address this.  The suggestions in the 
Stackoverflow are the best approach.

Original comment by jimcap...@google.com on 21 Jan 2014 at 7:11

[GoogleCodeExporter]

Quick update, we have an internal feature request for improved timezone 
handling and will update this issue when available.

Original comment by thomasp...@google.com on 22 Aug 2014 at 5:30

[GoogleCodeExporter]

Any update here?

Original comment by graham.p...@shinetech.com on 17 Nov 2014 at 1:26

[GoogleCodeExporter]

Unfortunately I can't give an ETA for this feature right now, but it's still 
something we'd like to add eventually.

Is there a particular use case you're trying to solve for?

Original comment by jcon...@google.com on 17 Nov 2014 at 5:59

[GoogleCodeExporter]

We power our sales dashboard using numbers from BQ, and would like to allow 
users to display sales in their time zone, not just UTC.

Original comment by and...@humblebundle.com on 17 Nov 2014 at 6:05

[GoogleCodeExporter]

Do you need to perform that conversion inside the query engine, or can you 
perform the appropriate time zone conversion on the query output before 
displaying it to the user?

Original comment by jcon...@google.com on 17 Nov 2014 at 6:10

[GoogleCodeExporter]

Well, say you want "SELECT client_tz_day, COUNT(*) FROM ... GROUP BY 
client_tz_day".

How can you do this client-side?

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.

Original comment by j...@celtra.com on 17 Nov 2014 at 6:19

[GoogleCodeExporter]

That's a fine example of a case where time zone conversion is needed in the 
query engine. :-) The client-side option is only reasonable when it's just a 
matter of display rendering.

If you have a small number of time zones that you can about, you can apply the 
time zone conversion manually (by adding/subtracting some amount of time), 
aggregate by day, and then optionally undo the conversion. But obviously that 
doesn't scale to lots of time zones, and it's hard to get all the time zone 
math correct, particularly if you want to be accurate with respect to daylight 
savings time and so on.

As mentioned above, we would like to provide this feature, but I can't give a 
specific ETA just yet.

Original comment by jcon...@google.com on 17 Nov 2014 at 8:49

[GoogleCodeExporter]

"can about" -> "care about"

Original comment by jcon...@google.com on 17 Nov 2014 at 8:50

[GoogleCodeExporter]

Doing the conversion and applying workarounds client side is just plain bad. We 
can't keep updating our client side code for different TZ's and whenever there 
is a daylight savings change.

Original comment by graham.p...@shinetech.com on 17 Nov 2014 at 9:09

[GoogleCodeExporter]

I understand, but unfortunately that's the best I can offer right now. It's 
helpful to hear that there's demand for this feature, and we'll see if we can 
get this fixed. But as mentioned above, I can't offer an ETA just yet.

Original comment by jcon...@google.com on 17 Nov 2014 at 9:23