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

Looker Q&A Looker
1

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

1 Like
2

But if your field has convert_tz: yes then whhy do you expect it not to convert it? It also depends on your Looker settings and your database settings. I’m also assuming the first field has convert_tz: no?

3
  1. No. As I said, I don’t have convert_tz in the original field, it’s the default for Looker. I guess for date the default is no timezone convert. Anyway because I already converted time zone before turning it into a date?
  2. It’s not that I did’nt expect conversion to happen, but I think it’s not converting correctly. After conversion the date must still be ‘2021-01-01’. In any case, why is the 1st date convert to timestamp? Even though I want to compare 2 dates? If both are date formats, then they would match, would they?

If timestamp_date is 2021-01-01 10:00:00 UTC, the date is supposed to be 2021-01-01 in my time zone

after time zone conversion it should be 2021-01-01 17:00:00, the date is still ‘2021-01-01’

The point is, for my use case, clearly 2 dates are the same day, but Looker output says they are not. And that cannot be right.

4

You can disable it by making it convert_tz: no

5

I need timezone conversion! But Looker is converting it wrong

If timestamp_date is 2021-01-01 10:00:00 UTC, the date is supposed to be 2021-01-01 in my time zone> > after time zone conversion it should be 2021-01-01 17:00:00, the date is still ‘2021-01-01’

6

Say, if something is 2020-12-31 23:00:00 UTC, the date is supposed to be ‘2021-01-01’ too.

7

It doesn’t look like this field: 2021-01-01 00:00:00 UTC is in UTC though if it takes 7 hours off since you are UTC+7.. Can you confirm your time zone settings (database, looker, user)?

8

I have a similar case and give a +1 that Looker creating the SQL for a customer dimensions seems buggy.

I have customer time dim that only does:

New dimension = coalesce(${some_date_1},${some_date_2},${some_date_3})

The explore is supposed to convert UTC to CEST. All dates are time group dims (with 1 and 2 being a DATE in the date base). All use (and should use) tz_convert.

If I use the dimensions standalone, the convert works as expected. But COALESCE over the 3 converts the dates to a time (with UTC to CEST adjustment, but coming from the DATE value) which is not expected:

The underlying sql does this which imho is interpreting the the convert when it should not be done (on dates):

…> > salesforce_opportunity.OPPORTUNITY_ID_C AS “salesforce_opportunity.opportunity_id_visible”,> (TO_CHAR(TO_DATE(CONVERT_TIMEZONE(‘UTC’, ‘Europe/Berlin’, CAST(salesforce_opportunity.RELATION_MIN_GO_LIVE_DATE_C AS TIMESTAMP_NTZ))), ‘YYYY-MM-DD’)) AS “salesforce_opportunity.first_relation_go_live_date”,> (TO_CHAR(TO_DATE(CONVERT_TIMEZONE(‘UTC’, ‘Europe/Berlin’, CAST(salesforce_opportunity.CUSTOMER_INTEGRATION_CURRENT_GO_LIVE AS TIMESTAMP_NTZ))), ‘YYYY-MM-DD’)) AS “so.customer_integration_current_go_live_date”,> (TO_CHAR(TO_DATE(CONVERT_TIMEZONE(‘UTC’, ‘Europe/Berlin’, CAST(salesforce_opportunity.WON_TIME_C AS TIMESTAMP_NTZ))), ‘YYYY-MM-DD’)) AS “salesforce_opportunity.opportunity_won_date”,> (TO_CHAR(DATE_TRUNC(‘second’, CONVERT_TIMEZONE(‘UTC’, ‘Europe/Berlin’, CAST(salesforce_opportunity.WON_TIME_C AS TIMESTAMP_NTZ))), ‘YYYY-MM-DD HH24:MI:SS’)) AS “salesforce_opportunity.opportunity_won_time”,> CASE WHEN TO_DATE(COALESCE((CONVERT_TIMEZONE(‘Europe/Berlin’, ‘UTC’, CAST(TO_DATE(CONVERT_TIMEZONE(‘UTC’, ‘Europe/Berlin’, CAST(salesforce_opportunity.RELATION_MIN_GO_LIVE_DATE_C AS TIMESTAMP_NTZ))) AS TIMESTAMP_NTZ))), (CONVERT_TIMEZONE(‘Europe/Berlin’, ‘UTC’, CAST(TO_DATE(CONVERT_TIMEZONE(‘UTC’, ‘Europe/Berlin’, CAST(salesforce_opportunity.CUSTOMER_INTEGRATION_CURRENT_GO_LIVE AS TIMESTAMP_NTZ))) AS TIMESTAMP_NTZ))), (CONVERT_TIMEZONE(‘Europe/Berlin’, ‘UTC’, CAST(TO_DATE(CONVERT_TIMEZONE(‘UTC’, ‘Europe/Berlin’, CAST(salesforce_opportunity.WON_TIME_C AS TIMESTAMP_NTZ))) AS TIMESTAMP_NTZ))))) = COALESCE((CONVERT_TIMEZONE(‘Europe/Berlin’, ‘UTC’, CAST(TO_DATE(CONVERT_TIMEZONE(‘UTC’, ‘Europe/Berlin’, CAST(salesforce_opportunity.RELATION_MIN_GO_LIVE_DATE_C AS TIMESTAMP_NTZ))) AS TIMESTAMP_NTZ))), (CONVERT_TIMEZONE(‘Europe/Berlin’, ‘UTC’, CAST(TO_DATE(CONVERT_TIMEZONE(‘UTC’, ‘Europe/Berlin’, CAST(salesforce_opportunity.CUSTOMER_INTEGRATION_CURRENT_GO_LIVE AS TIMESTAMP_NTZ))) AS TIMESTAMP_NTZ))), (CONVERT_TIMEZONE(‘Europe/Berlin’, ‘UTC’, CAST(TO_DATE(CONVERT_TIMEZONE(‘UTC’, ‘Europe/Berlin’, CAST(salesforce_opportunity.WON_TIME_C AS TIMESTAMP_NTZ))) AS TIMESTAMP_NTZ)))) THEN TO_CHAR(COALESCE((CONVERT_TIMEZONE(‘Europe/Berlin’, ‘UTC’, CAST(TO_DATE(CONVERT_TIMEZONE(‘UTC’, ‘Europe/Berlin’, CAST(salesforce_opportunity.RELATION_MIN_GO_LIVE_DATE_C AS TIMESTAMP_NTZ))) AS TIMESTAMP_NTZ))), (CONVERT_TIMEZONE(‘Europe/Berlin’, ‘UTC’, CAST(TO_DATE(CONVERT_TIMEZONE(‘UTC’, ‘Europe/Berlin’, CAST(salesforce_opportunity.CUSTOMER_INTEGRATION_CURRENT_GO_LIVE AS TIMESTAMP_NTZ))) AS TIMESTAMP_NTZ))), (CONVERT_TIMEZONE(‘Europe/Berlin’, ‘UTC’, CAST(TO_DATE(CONVERT_TIMEZONE(‘UTC’, ‘Europe/Berlin’, CAST(salesforce_opportunity.WON_TIME_C AS TIMESTAMP_NTZ))) AS TIMESTAMP_NTZ)))), ‘YYYY-MM-DD’) ELSE TO_CHAR(DATE_TRUNC(‘second’, COALESCE((CONVERT_TIMEZONE(‘Europe/Berlin’, ‘UTC’, CAST(TO_DATE(CONVERT_TIMEZONE(‘UTC’, ‘Europe/Berlin’, CAST(salesforce_opportunity.RELATION_MIN_GO_LIVE_DATE_C AS TIMESTAMP_NTZ))) AS TIMESTAMP_NTZ))), (CONVERT_TIMEZONE(‘Europe/Berlin’, ‘UTC’, CAST(TO_DATE(CONVERT_TIMEZONE(‘UTC’, ‘Europe/Berlin’, CAST(salesforce_opportunity.CUSTOMER_INTEGRATION_CURRENT_GO_LIVE AS TIMESTAMP_NTZ))) AS TIMESTAMP_NTZ))), (CONVERT_TIMEZONE(‘Europe/Berlin’, ‘UTC’, CAST(TO_DATE(CONVERT_TIMEZONE(‘UTC’, ‘Europe/Berlin’, CAST(salesforce_opportunity.WON_TIME_C AS TIMESTAMP_NTZ))) AS TIMESTAMP_NTZ))))), ‘YYYY-MM-DD HH24:MI:SS’) END> AS new_dimension,> > …