We have clients who are in different time zones, but their data will be stored in the same Postgres DB. We want to offer our clients full self-service reporting using the question builder in the UI (not SQL). Is there any way to have users select a time-zone in Metabase and have it globally set the time-zone for that user? Is there a way to pass a variable to Postgres from Metabase so that Postgres uses the variable in say a filter at runtime? I have been looking at options of accomplishing user level time zones in Metabase and it always keeps circling back to the SQL query builder with variables which will not work since our users are not technical at all.
Any workarounds, guidance or information about current features would be much appreciated!!
At least in the OSS version, there is no provision for per-user timezones. You can set Metabase to either use the databaseās timezone or use a specific timezone.
If your clients are global then consider using UTC.
Since you mention this is not available in OSS, is there an ability to do this in the paid version?
Is this considered out of scope for metabaseās goals?
I have the same question as the original poster because we show some metabase dashboards to customers, embeded in our application. Weād like them to be able to see their data in their own timezone. We have customers in different timezones. Currently the data is stored in UTC. Weād like the ability to change the report timezone variable on a per dashboard or even per question basis (or let it be set as a filter value), rather than only being able to choose it once at the top level in metabase admin. Is metabase just the wrong tool for this?
Iām not seeing any timezone settings per-user in the docs for any edition.
Time zone management is complicated, more so as support varies amongst database engines (and isnāt supported at all in some). Metabase documents the report timezone setting here and some of the pitfalls here.
Itās certainly possible to make time zones a filter/variable, but it takes careful query writing.
Set the Metabase Report Timezone to match the timezone you want to see your reports in
This is exactly the feature Iād like to see, except instead of only being able to set it at the top level in admin settings, why not be able to set it anywhere? If report timezone is just adding sql that sets the timezone in the session, why not let that by usable (as long as the underlying db and data supports it). I have my data in UTC in a postgresql timestamp with timezone field. It would be nice for metabase to support viewing those timestamps in any timezone of the userās choosing, as opposed to only in whatever timezone is set at the top level by an admin.
Maybe what Iām asking isnāt actually as simple as it seems for some reason , so Iām happy to learn why itās not possible.
Anything is possible given infinite money and timeā¦
I get the frustration. Iāve been developing UNIX software for 30 years and still screw up time zones and time zone offsets. Itās a difficult, sticky, nasty problem, and thatās on platforms that have used the IANA timezone database for forever, which generally does a good job. Windows only recently adopted it.
And we havenāt even started talking about what happens to time series that cross DST boundaries. It happens twice a year yet everyone still screws THAT up! Good luck finding a charting package that even TRIES to deal with it!
It would be so much nicer if computers didnāt have to interface with humans, who insist on having things printed in local time. Is it so hard to internalize that the sun comes up at 1300Z and goes down at 0300Z in your area? Well, MY area anywayā¦
Practically speaking, the first level problem is that user settings that change data arenāt compatible with query caching. That would need to be sorted first, but thereās a lot of layers to the onion.
Iāll be the first to agree that timezones and dates in general are quite complicated to handle correctly. But once the data itself follows a single timeline such as a UNIX ms timestamp offset from epoch or UTC normalized time data, when you are viewing that data, it should be possible to use the IANA database along with a set of functions to view/retrieve that data in any localized timezone. This is my understanding of what setting the timezone for a db session on postgres already does. Someone on the postgres project has already done the work to convert the timestamps appropriately, and I sincerely thank them for their work. Looking at other comparable BI or analytics display tools that support user or dashboard level timezone settings, they do all have these prerequisites (data must be normalized to UTC, database must support timezones, etc.), but once those are met, timezones can be configured at a more granular level than admin/account wide.
I saw the issues you linked before, so seems like this is a common request, and has been for 8+ years.
I, too, wish humans could just do away with all these silly things, as you put itā¦but alas, our customers must see their data in their own timezone. Other software and tools they use support this, so we must as well. My original goal here was just to figure out if Metabase could support what we need to do. I would have started my own thread since the current threads, issues, and roadmap donāt directly address this, but I saw this recent thread so decided to comment on it instead. For what itās worth, this is something weād be willing to pay for a solution to, so Iām not trying to demand something for free. But at the end of the day, I do have to evaluate if our current processes and/or tools can meet our customer needs.
The solution we use for now will probably be to create columns in metabase that use convertTimezone to convert the relevant timestamp columns to the few timezones our customers care about, which right now are mostly US timezones. Then, on dashboards we can create a date picker that operates on the converted column and hide the original column. Might mean we end up having to create duplicate dashboards and some additional overhead, but there doesnāt seem to be a better option.
We donāt need the more complex architecture outlined in one of the threads with display timezone vs query timezone (and maybe third?), all being separately configurable. It would be enough to simply allow viewing of the same dashboard in a few different timezones, where the actual conversion is handled by the db itself.
Has anybody looked at using the new database routing feature to use a different connection based upon the userās timezone? theyād have to be assigned manually but could possibly have their timezone set as part of the connection string or user account.
It's possible to do a setup (Pro/Enterprise) with Custom Sandboxing.
You create sandboxing questions over each table, and instead of the original dates from each table, you display a timezone-adjusted version of the date, and you can use a SQL filter for the desired timezone.
Then, in the group with sandboxed access, you set a "timezone" user attribute and map it to the "timezone" filter in the question. You can make sure to add this to your JWT login/SSO configuration.
This will make that group see an edited version of the table with timezone adjustments, and this works with filtering and UI questions (but won't affect native SQL queries)
Routing could also be a possibility but I have not yet seen it used to achieve this effect. Good thing about Sandboxing is that it doesnāt require modifying any questions or dashboards. Some cons, though:
Requires Pro/Enterprise
Some DBs like Athena require quite a bit of brain work to get this ātimezone adjustmentā query correctly (I had to format a date, do AT TIME ZONE, remove the timezone part from the string, and then parse it again, ugh )
If thereās an index on the date and weāre adjusting its timezone, we might end up making the DB engine skip the index altogether.
In MySQL there are type issues with sandboxing like this one, and if your date is a TIMESTAMP and you want to CONVERT_TZ it, it wonāt let you sandbox it because it becomes a DATETIME - youāll need to create something like a DB view which is also annoying
Not saying itās the best solution at all, but itās worth considering. Also, DB routing with timezone per DB connection could be an interesting solution too if the DB supports it.
Interested to try the db routing approach. Is it possible to set timezone in a way that will override the report timezone setting?
I will look at custom sandboxing as well. I did see that mentioned on one of the github threads but it sounded like a lot of overhead. I can tinker with it though.
I donāt know, it was just a suggestion. One of the advantages of working mostly in the UK is that I only need to worry about summer vs winter time. The actual time zones arenāt an issue!
That said, Iām currently working with a customer in Belgium with an office in Thailand. Theyāre not using Metabase but still having a few problems.
Hi Guys, so have any of you tried the db routing option? I donāt think that the data sandboxing will work for us since each of the clients on our multitenant setup has different custom columns set up which means we heavily make use of Metabaseās jsonb unfolding with Postgres. Metabase does a great job of identifying column types and even casting and converting the date fields in the nested jsonb to the global timezone set at admin level in the localization settings.
@AndrewMBaines how would one change the timezone using database routing? Do you mean that you would have different dbās for different timezones?
EDIT: This doesnāt work for PostgreSQL & PGJDBC, it always uses the JVM timezone regardless.
Looks like you could do this using db routing. One way to do it is to create multiple routes to the same database but vary the database user Metabase logs in as based on the desired session timezone.
Youād set up PostgreSQL users that correspond to timezones and map them using a user attribute or JWT claim. Youād end up with users for each timezone, created like:
CREATE USER dbuser_America_LosAngeles;
ALTER USER dbuser_America_LosAngeles SET TimeZone='America/Los_Angeles';
CREATE USER dbuser_America_Denver;
ALTER USER dbuser_America_Denver SET TimeZone='America/Denver';
...
Now you have a stack of database users with overridden timezones. Leave the Metabase global timezone setting at ādatabase defaultā so it doesnāt mess with the session timezone; itāll inherit the userās timezone instead. When Customer X logs in, it sees a attribute set to āGive them Eastern timeā and DB routing routes them through the connection that logs in as dbuser_America_NewYork.
Itās a gross hack, you end up with extra DB connections, but it works.
This probably only works with PostgreSQL or any DBMS that lets you set session parameters as part of the user record.
Thanks for this @dwhitemv . Have you tested this in Metabase? I am testing this and I have created a user with the timezone set. When logging in as that user in an ide the timezone converts no problem. The issue is: It looks like Metabase overrides the user timezone when I try and query the database using the connection I createdā¦
This connection is set up with the timezone (nzst) user:
No, I didnāt actually test it, but clearly should have. Thatās what I get for making assumptions about how PGJDBC works based on libpq behavior.
I read through the PGJDBC source and the only way to set the connection timezone is:
App calling TimeZone.setDefault(TimeZone.getTimeZone("UTC")); before instantiating the JDBC object (but this changes the default timezone application-wide); or
Setting the JVM timezone on the command line via -Duser.timezone="UTC" or via environment variable (which also changes the default timezone application-wide).
That leaves us with Metabase doing the time zone adjustments itself, and thus issue #4284 lives on.
Best way forward appears to be advanced sandboxing/RLS for the Metabase UI and a hidden filter parameter with the desired timezone and support in the query to use it for embedded dashboards.
)
