CODEWITHSUNDEEP
google-bigquery
Viktor Kulikov
Viktor Kulikov

Reputation: 269

DateTime offset in Google BigQuery

I have some trouble with Google BigQuery

I need to build result in timezone UTC+05:45, but i get this error

DATE_ADD 2nd argument must have INT32 type.

query example

SELECT DATE(DATE_ADD(time, 5.75, 'HOUR')) AS day, ... FROM ... WHERE ... AND  ( DATE_ADD(time, 5.75, "HOUR") >= '2015-05-01 00:00:00' AND  DATE_ADD(time, 5.75, "HOUR") < '2015-06-01 00:00:00' ) GROUP EACH BY day  ORDER BY day asc LIMIT 2000

This code correctly work with any others offsets (+03:00, -05:30, etc), but with +05:45 - i get this error

Do you have any ideas about that?

Upvotes: 2

Views: 1545

Answers (1)

Pentium10
Pentium10

Reputation: 207830

If you are 5 hour behind UTC, you need to use a workaround:

UTC_USEC_TO_DAY(timestamp_field- (5*60*60*1000*1000000) )

Timezone functions, is on feature request list as documented here: https://code.google.com/p/google-bigquery/issues/detail?id=8

Upvotes: 3

Related Questions