looker date filter with timezones converting from datetime to timestamp

I have created a date filter and then want to use the start and end date to filter a derived table.

 

filter: date_filter {
type: date_time
datatype: datetime
convert_tz: yes
}

 

we currently have user specified timezone turned on so the relative dates eg 'today','yesterday' can be worked out based on each users timezone. we are using bigquery and when we look at the generated sql for the  filter  using {% date_start date_filter %} we are getting the following bigquery code. 

 DATETIME(TIMESTAMP(DATETIME_TRUNC(CURRENT_DATETIME('Pacific/Chuuk'), DAY), 'Pacific/Chuuk'))

what the code is doing get the correct datetime base on a users timezone , then gets the date only but then converts to timestamp which now gives the UTC time and not the time in timezone and then converts back to a datetime but based on the UTC therefore giving the wrong date. why does looker seem to be doing so many conversions?

How can we allow a user to pass the date based on their time zone through to a database which has timezones already converted?

 

 

1 0 658
0 REPLIES 0
Top Labels in this Space
Top Solution Authors