And in this example, we use a date in TIMESTAMP format (let's call it raw_date), expecting this setup above convert all dates to our timezone format(UTC-3), especifically to “America/Sao_Paulo”.
But what we see is that, like the example below (taking any count per hour of the day and ordering it), we are facing an delay of 3 hours. Expected 28 junho, 2023, 03:00 to be 28 junho, 2023, 00:00 and so on.
We think we are closer the origin of the problem: the account report timezone configuration at admin settings works only for grouped date field, but not for filtered date field.
In the exemple bellow, we used the same date field session_started_at to filter and to group by. Nevertheless, looking at the SQL generated, we have diferent behaviors:
date field at WHERE clause were converted to America/Sao_Paulo timezone (account report zone configured);
date field at SELECT clause were NOT converted to America/Sao_Paulo timezone
SELECT datetime_trunc(datetime(`source`.`session_started_at`), day) AS `session_started_at`, count(distinct `source`.`domain_sessionid`) AS `count`
FROM (select
session_started_at,
domain_sessionid
FROM `livup_master.sessions`) `source`
WHERE (`source`.`session_started_at` >= timestamp_trunc(timestamp_add(current_timestamp(), INTERVAL -1 day), day, 'America/Sao_Paulo')
AND `source`.`session_started_at` < timestamp_trunc(current_timestamp(), day, 'America/Sao_Paulo'))
GROUP BY `session_started_at`
ORDER BY `session_started_at` ASC
