Field filtering on a timestamp with timezone only reporting in UTC

Get Help
1

Our database is in UTC, but we operate in PST. All of the times work correctly if I do AT TIME ZONE 'america/los_angeles' when doing my queries, however I really like to use the field filters and so do my coworkers. For the past few weeks we've been having to deal with what I thought was a database issue on our side, however it seems like the field filters are the cause. Basically, when using them to say filter on June 1st, the day will 'start' at 5pm PST. See below:

image

However, when writing out the WHERE statement myself, I can achieve the correct time. See below: (can only post one image apparently)

Is there anyway to apply a time zone to datetime field filters?

4 Likes
2

Hi @zsmith2
Please post “Diagnostic Info” from Admin > Troubleshooting.
And which database are you querying?

I would guess that you’re having the same behavior using the filters in Simple/Custom question, so it’s not specific to Field Filter?

While a lot of timezone issues were fixed in 0.34, there are still a few left:
https://github.com/metabase/metabase/issues?q=is%3Aopen+is%3Aissue+label%3AMisc%2FTimezones+filter

3

I’m having exact the same problem, I would like to change the timezone of my datetime field filters.
Is this possible in any way?

4

@Cappie2707
Please post “Diagnostic Info” from Admin > Troubleshooting.
And which database are you querying?

5

Don’t know if I completely caught your problem, but I just faced something similar today. I solved by checking the actual executed query on postgres and found that it expands to

CAST("schema"."table"."column" AS date) = $1

and, of course, uses the driver/pg to expand $1 to the set value. It is actually a simple string replacement so you can add a the time zone statement right after like this:

{{filterName}} AT TIME ZONE 'your time zone'

The solution is unfortunately strongly coupled with the Metabase implementation, but solves the problem anyway :slight_smile: .

Hope it helps

Regards,
Heitor

1 Like
6

@flamber, I'm facing the same issue.

The solution proposed by @heits didn't work for me because after the Metabase`s "query generator" generate the query, the time condition stands between parentheses, like in the example below:

WHERE (partner_id = '5123124-32e343d-5235-8e355329-9370cd503016'
   AND time < timestamp "2021-03-26 00:00:00 Etc/UTC")

So when I add "AT TIME ZONE" after the parameter I get the following error:

image
image750Ă—404 16.6 KB

I presume that the query generator build something like this:

WHERE (partner_id = '5123124-32e343d-5235-8e355329-9370cd503016'
       AND time < timestamp "2021-03-26 00:00:00 Etc/UTC") AT TIME ZONE 'America/Sao_Paulo'

Which is obviously wrong.

I'm posting below the Diagnostic Info as requested, and the database where I'm querying is the Google BigQuery.

{
  "browser-info": {
"language": "en-US",
"platform": "MacIntel",
"userAgent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 11_2_3) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/89.0.4389.114 Safari/537.36",
"vendor": "Google Inc."
  },
  "system-info": {
"file.encoding": "UTF-8",
"java.runtime.name": "OpenJDK Runtime Environment",
"java.runtime.version": "11.0.9.1+1-post-Debian-1deb10u2",
"java.vendor": "Debian",
"java.vendor.url": "https://tracker.debian.org/openjdk-11",
"java.version": "11.0.9.1",
"java.vm.name": "OpenJDK 64-Bit Server VM",
"java.vm.version": "11.0.9.1+1-post-Debian-1deb10u2",
"os.name": "Linux",
"os.version": "4.19.0-14-cloud-amd64",
"user.language": "en",
"user.timezone": "Etc/UTC"
  },
  "metabase-info": {
"databases": [
  "bigquery"
],
"hosting-env": "unknown",
"application-database": "postgres",
"application-database-details": {
  "database": {
    "name": "PostgreSQL",
    "version": "12.6"
  },
  "jdbc-driver": {
    "name": "PostgreSQL JDBC Driver",
    "version": "42.2.18"
  }
},
"run-mode": "prod",
"version": {
  "date": "2021-03-03",
  "tag": "v0.38.1",
  "branch": "release-x.38.x",
  "hash": "79ef63a"
},
"settings": {
  "report-timezone": "America/Sao_Paulo"
}
  }
}

Thank you all for sharing, guys, hope we get some fix for this soon, something like a "timezone picker" on the variables properties.

1 Like
7

Hey Lucas, I realize I never added my personal 'fix' for this in the meanwhile.

What works for me is casting my own variables as either timestamp or date i.e:

WHERE your_time_column AT TIME ZONE 'whatever_timezone' BETWEEN CAST({{date_start}} AS TIMESTAMP) AND CAST({{date_end}} AS TIMESTAMP)

I noticed as well that doing the same thing but with {{}}::TIMESTAMP doesn't work for whatever reason with variables, you have to specifically cast it.

8

@flamber I am having the same issue as above. My diagnostic info is below. For context, we are using Redshift as our warehouse and also have a postgres database that is accessed through Redshift via a federated query. Our data is stored timestamps in UTC, but we operate in and localize to PST. It appears that the date filters are being applied to the data in UTC before the timestamps are localized to PST, resulting in the filter including some records from the day prior to be included (as 4:00pm PST is midnight UTC, so anything after 4:00pm PST is being counted as "tomorrow" in the filter) [Screenshot below]. One thing to note, when we query the postgres database directly with date field filters, this issue is not present - only when we query through Redshift with date field filters. This is causing issues when explicit date ranges are set as data for the last date in the range is being cutoff at 4:00pm PST (potentially excluding relevant data). Is there anything I can do to resolve this?

Thank you in advance for any support!

Screenshot 2023-06-28 at 10.27.24 AM

{
  "browser-info": {
    "language": "en-US",
    "platform": "MacIntel",
    "userAgent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/114.0.0.0 Safari/537.36",
    "vendor": "Google Inc."
  },
  "system-info": {
    "file.encoding": "UTF-8",
    "java.runtime.name": "OpenJDK Runtime Environment",
    "java.runtime.version": "11.0.19+7",
    "java.vendor": "Eclipse Adoptium",
    "java.vendor.url": "https://adoptium.net/",
    "java.version": "11.0.19",
    "java.vm.name": "OpenJDK 64-Bit Server VM",
    "java.vm.version": "11.0.19+7",
    "os.name": "Linux",
    "os.version": "4.14.314-237.533.amzn2.x86_64",
    "user.language": "en",
    "user.timezone": "GMT"
  },
  "metabase-info": {
    "databases": [
      "postgres",
      "redshift"
    ],
    "hosting-env": "unknown",
    "application-database": "postgres",
    "application-database-details": {
      "database": {
        "name": "PostgreSQL",
        "version": "12.11"
      },
      "jdbc-driver": {
        "name": "PostgreSQL JDBC Driver",
        "version": "42.5.1"
      }
    },
    "run-mode": "prod",
    "version": {
      "date": "2023-05-24",
      "tag": "v0.46.4",
      "branch": "release-x.46.x",
      "hash": "f858476"
    },
    "settings": {
      "report-timezone": "US/Pacific"
    }
  }
}