I have this fairly simple dimension I use for filtering
but the time zone conversion is still applied if the field is used as a filter:
((TIMESTAMP_ADD(TIMESTAMP_TRUNC(TIMESTAMP(FORMAT_TIMESTAMP('%F %H:%M:%E*S', CURRENT_TIMESTAMP(), 'America/New_York')), DAY), INTERVAL -6 DAY)))
Big deal for me as it basically stops me from fully parameterising a historical table that has a “date” field + calendar table that also has a “date” field. Both do not have hour granularity, which means the offsets are not needed.
Because of those conversion to timestamps there are a lot of DATE vs TIMESTAMP errors that can’t even be solved with putting CAST(date AS TIMESTAMP), which I hate to do anyway