When is convert_tz: no ignored?

[kaufmannie]

Looker documentation highlights the below nuance about using convert_tz: no. However, in my testing, I do not think that I am observing the behavior outlined in the Looker doc. I am wondering if I am misunderstanding. Can someone please help me understand when we can expect convert_tz: no on a dimension to be ignored? 

The behavior of having data unexpected included/excluded in the dataset is something that we will certainly want to avoid as much as possible. However, our ideal connection configs are for the database timezone to be UTC and the query time zone to be America/NY. With these settings, we NEED convert_tz: no on certain fields (when a dimension_group with type: time is applied on top of a source column that is a true date). 

--- Looker doc:

convert_tz: no applies only to a dimension, not to a filter that uses the dimension. In other words, filters always perform time zone conversion. When you specify convert_tz: no, time-based data values are displayed in the database time zone, but are filtered using the query time zone.

Because filters always do time zone conversion, a difference between the database time zone and query time zone could cause data to unexpectedly be included or excluded from a dataset. To avoid this, ensure that the query time zone is set to the same value as the database time zone.

---

Investigation/testing: 

Our Database Timezone = UTC and our Query Timezone = America/NY. The User Specific Timezones are disabled.

EXAMPLE: convert_tz applied properly when dimension is used in both a normal filter and a custom filter. You can see the nether filter appears to have conversion applied

The invoice_period_start dimension is set up as follows: 

dimension_group: invoice_period_start {
    type: time
    timeframes: [raw, date, week, month, year, quarter, week_of_year, month_num]
    sql: ${TABLE}."INVOICE_PERIOD_START_DATE" ;;
    description: "The start period of time where charges and store actions are attributed to the invoice"
    group_label: "Invoice Period Started At"
    drill_fields: [detail*]
    convert_tz: no # source field is a date, we do not want a conversion after 00:00:00 time arbitrarily applied
  }

While the dimension is set with convert_tz: no, I would expect to see this ignored in the filter logic per Looker docs. In other words, I would have expected the timezone conversion would be applied given Looker doc says "filters always perform time zone conversion". However when testing filtering on the above dimension, I do not see the timezone conversion applied: 

^ ^if timezone conversions were actually happening, I would expect to see the below in the WHERE clause (based on what i see in testing fields with convert_tz: yes). 

((( fact_invoice.”FIRST_POSITIVE_INVOICE"  ) >= ((CONVERT_TIMEZONE('America/New_York', 'UTC', CAST(TO_TIMESTAMP('2023-09-13') AS TIMESTAMP_NTZ)))) AND ( fact_invoice."FIRST_POSITIVE_INVOICE"  ) < ((CONVERT_TIMEZONE('America/New_York', 'UTC', CAST(DATEADD('day', 1, TO_TIMESTAMP('2023-09-13')) AS TIMESTAMP_NTZ))))))

Can someone please help me understand what to expect for convert_tz: no? Can a difference between the database time zone and query time zone actually cause data to unexpectedly be included or excluded from a dataset? And if so, how exactly does that happen? (Because it doesnt seem like its the result of timezone conversions happening in the filter but not in the selection ... unless im missing something!)