[Bug] Custom Dimension with Timezones - apply timezone after truncate to date

[Trang]

So I know Looker suggests go through support for bug report. But I’ve reported this bug and all I received from Looker support is that it's not a bug, it's how Looker is supposed to run. That's why I'm asking if anyone else run into the same thing and if so do you guys consider this a bug. I feel like I'm going insane with "ït's the way Looker is supposed to work" from support.

My problem appears when I try to compare a date - date_date (dimension_group, type: time, datatype:date) with another date - timestamp_date ( (dimension_group, type: time, convert_tz: yes) in Custom Measure. I use this comparison in my filter. For the first date I don't specify convert_tz because it's a date format, and I've converted before turning timestamp into date already. 

My language is Standard SQL (we use BigQuery). I need to compare and see if the 2 dates are the same. The auto-generated SQL is as follow

(TIMESTAMP([date_date1] )) = (TIMESTAMP_TRUNC(CAST([timestamp_date]  AS TIMESTAMP), DAY, 'my_time_zone'))
 

So the problem with this auto generated SQL is:

If date_date is 2021-01-01 (TIMESTAMP([date_date1] )) = 2021-01-01 00:00:00 UTC

If timestamp_date is 2021-01-01 10:00:00 UTC, the date is supposed to be 2021-01-01 in my time zone, but the function returns 2020-12-31 17:00:00 UTC. (I'm at GMT + 7, so it deducts 7 hours to convert time zone after truncate)

And in this case the 2 dates are supposed to be the same. But Looker would show that they are not.

I'm wondering if anyone else has the same problem and how you mitigate it. For now I had to parse date from timestamp using custom sql and specify convert_tz: no. Many of the views that I maintain have this problem, so a suggestion of a faster fix other than manually converting time zone and turn off Looker time zone convert would be greatly appreciated. 

But to be honest I don't think this is how it's supposed to work. I think Looker is supposed to enable us to work without explicitly convert time zone every step on the way. 

Thanks