Timezone queries

Shantesh · April 3, 2025, 8:23am

I read this documentation about metabase timezone Understanding Timezones in Metabase · metabase/metabase Wiki · GitHub

What I understood that,

Metabase uses report timezone set on admin settings rather than relying on browser timezone ?
The report timezone is set for all users on the instance once set by admin. The users in different region will retrieve the data as per report timezone set?
How can we set user specific timezone?
The report timezone supports only postgresql , how the timezone display works for other databases?

TonyC · April 4, 2025, 11:00am

I would recommend you look into this article which is more recent :

Yes
Yes
Metabase doesn't handle multiple timezones well. In fact I will link the following feature requests:

github.com/metabase/metabase

Emit time zone conversions directly in queries instead of using session/connection/query time zones

opened 06:32PM - 12 Dec 22 UTC

camsaul

Querying/Processor Misc/Timezones .Backend

Part of #27178 For local temporal columns (temporal columns that without time… zone or offset information, like `datetime` or `date` or `timestamp [without time zone]`) we mostly rely on the `report-timezone` Setting, and do something like ```sql SET SESSION TIME ZONE = 'US/Pacific'; ``` before running queries to make sure all of these not-zoned columns are interpreted as being in our desired time zone. This is undesirable for a few reasons: - We don't necessarily want to treat every local temporal column in the query as being in the same time zone -- see #6439 for more information - Only a fraction of our DBs support "session time zones" which means time zone support is somewhat broken on DBs like BigQuery. - - Other DBs cannot set session time zone on read-only connections, so we cannot make them read-only when we'd like to - Some databases can only set session time zone with connection properties which means we have to destroy our connection pool if the zone changes and cannot support different zones for different users (#4284) - Performance impact: if we execute `SET SESSION TIME ZONE` every time we run a query we actually have to run two queries instead of one. So this slows things down a bit - Session time zones potentially screw with sync because the values that come back can change when if the session time zone changes. For example when fingerprinting a `datetime` column, the min and max value would change if the `report-timezone` changes ### Proposed change to query compilation Change the way we compile local temporal columns to include the time zone conversion, e.g. `AT TIME ZONE`. So ```sql -- before running the query SET SESSION TIME ZONE = 'US/Pacific'; -- actual query SELECT my_datetime_column ... ``` becomes ```sql SELECT my_datetime_column AT TIME ZONE 'US/Pacific' ... ``` (in Postgres). For the time being `US/Pacific` can continue to come from the `report-timezone` Setting. This should be easy enough to do when compiling `:field` clauses in MBQL; we can build on the recent work @qnkhuat did in #25698 and #26633 ### Results The other thing we do with `report-timezone` is coerce the offsets of all values in the results to that time zone. For example if you have a `timestamp with time zone` column and the data warehouse returns a value ``` 2022-12-12T10:26:00-08:00 ``` but your `report-timezone` is `US/Eastern`, the `format-rows` QP middleware will "adjust" this value to ``` 2022-12-12T13:26:00-05:00 ``` This behavior is a little bit questionable, but I think we can worry about that separately from changing query compilation. So we shouldn't need to do anything about that here. See #14056 for more info ### Open Questions The one downside to this change is that native queries would no longer get the `SET SESSION TIME ZONE` stuff applied to them. I think this is fine, because if people are writing native queries they can always include whatever `AT TIME ZONE` they wish, if they need something to be interpreted as being in some specific time zone; if not then they can work with the local temporal types without worrying about zones at all. The desired report timezone would still be applied by the `format-rows` middleware to columns that we read out as `LocalDate`, `LocalTime`, or `LocalDateTime`, so it wouldn't affect the way we display results.

github.com/metabase/metabase

Convert user timezone to db timezone when filtering

opened 08:48PM - 13 Feb 24 UTC

paoliniluis

Type:New Feature Misc/Timezones Querying/Parameters & Variables

**Is your feature request related to a problem? Please describe.** If we captur…e or set the user timezone, we can convert the user timezone to the reporting timezone so all users see the same data when searching for the same date/time range **Describe the solution you'd like** 1) capture or allow to set user timezone https://github.com/metabase/metabase/issues/4284 2) convert the timezone at the query level, when there are users in different timezones **Describe alternatives you've considered** None, filters will convert everything at the reporting timezone **How important is this feature to you?** Requested probably a lot of times, so it's about time we create a feature request for this **Additional context** +1 to https://github.com/metabase/metabase/issues/5640

github.com/metabase/metabase

Proposal: Allow user to set desired Timezone

opened 04:43PM - 01 Feb 17 UTC

jstenquist

Priority:P2 Type:New Feature Misc/Timezones

Our users of Metabase would like to query and view data in their local timezone …(US/Eastern). I have tried using the Timezone setting in Admin but it does not produce desired results. - MySQL timezone is UTC - Data is stored in UTC - Our staff is in US/Eastern If I set timezone to UTC in Admin, it reports data correctly in UTC. If I set timezone to Eastern, data is reported 5 hours earlier than Eastern time, and 10 hours earlier than UTC. Ideally, as an admin I could set the Timezone to UTC and allow a user to set their own timezone, or alternatively just choose to show all data in Eastern time since all of ours users are in that timezone. --- - Your browser and the version: Chrome 55.0 - Your operating system: MacOS 10.12.2 - Your databases: MySQL - Metabase version: 0.22.2 - Metabase hosting environment: Elastic Beanstalk - Metabase internal database: Postgres :arrow_down: Please click the :+1: reaction instead of leaving a `+1` or `update?` comment

Not sure i understand this question but the reporting timezone will apply to any column that supports timezone

Shantesh · April 4, 2025, 11:36am

Thanks Tony for response.
4th point was with repect to this doc which mentions "Doesn't support setting report timezone" for some databases.

TonyC · April 4, 2025, 11:44am

That is an old doc build back in 2019 ... Use the online docs in the metabase site:

If a database is using date/time columns without any timezone information that's when this won't work. Example Mongo for instance, columns being Date or Timestamp without timezone

Shantesh · April 4, 2025, 11:49am

Okay got it, Thanks alot Tony