[Help] Models can't fit the same data for summarize and filter

Hi Guys,

We've got a difference between of 3h less than us-location(UTC-3).
And We set up our Metabase configs for that, see:

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.

In other words, not respecting our set up:

Summarizing, We try to create a new field inside the Model, let's call it converted_**date**:

TIMESTAMP(FORMAT_TIMESTAMP("%Y-%m-%d %H:%M:%S", sessions.session_started_at, "America/Sao_Paulo")) AS session_started_at,

But it returns the same result!

It only works when we use:

  • raw_date to filter (TIMESTAMP without changes)
  • converted_date to summarize (converted TIMESTAMP)

And We get to the expected outcome, bringing data since 00H:

So, summarizing: We hopped use the same date to filter and summarize. Does anyone knows how to solve that or any tip that may explain why this happen?


troubleshooting info? which DW is this one?

Hey Luiggi,

We work here with BigQuery from GCP

Troubleshooting info here:

"browser-info": {
"language": "en-GB",
"platform": "MacIntel",
"userAgent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/16.1 Safari/605.1.15",
"vendor": "Apple Computer, Inc."
"system-info": {
"file.encoding": "UTF-8",
"java.runtime.name": "OpenJDK Runtime Environment",
"java.runtime.version": "11.0.18+10",
"java.vendor": "Eclipse Adoptium",
"java.vendor.url": "https://adoptium.net/",
"java.version": "11.0.18",
"java.vm.name": "OpenJDK 64-Bit Server VM",
"java.vm.version": "11.0.18+10",
"os.name": "Linux",
"os.version": "5.15.109+",
"user.language": "en",
"user.timezone": "GMT"
"metabase-info": {
"databases": [
"hosting-env": "unknown",
"application-database": "postgres",
"application-database-details": {
"database": {
"name": "PostgreSQL",
"version": "14.5"
"jdbc-driver": {
"name": "PostgreSQL JDBC Driver",
"version": "42.5.1"
"run-mode": "prod",
"version": {
"date": "2023-04-05",
"tag": "v0.46.1",
"branch": "release-x.46.x",
"hash": "12a6e1d"
"settings": {
"report-timezone": "America/Sao_Paulo"

Guys, any update here?

Hi guys!

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
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

@flamber, have you ever seen that?

We tried to duplicate this issue using Sample Database, but it worked without errors. Maybe is an especific problem with Big Query database?


Please upgrade to the latest version, there’s a bug currently for this issue that I’m not able to find right now in the issue tracker

We are running the latest version, but the problem continues.