Field filtering on a timestamp with timezone only reporting in UTC

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?

3 Likes

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

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?

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

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

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

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

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.