Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Metabase should allow user-provided timezones at the column level #6439

Open
senior opened this issue Nov 21, 2017 · 5 comments

Comments

Projects
None yet
5 participants
@senior
Copy link
Member

commented Nov 21, 2017

Most SQL databases have some concept of a timestamp field that does NOT include it's original timezone. The database that this is probably most obvious is MySQL. When using datetime columns, there are no adjustments from the original storage timezone to the currently specified timezone. For these datatypes, the database is making the assumption that the user knows what timezone the timestamp should be in. From Metabase's perspective, we can't know what the user has intended for that column. As an example, let's assume that the database is in UTC with a timestamp of 2017-11-20 23:24:06, and the user is in US/Pacific time. Depending on how the user is interpreting that timestamp, they might want to see that timestamp adjusted to US/Pacific, which would be 2017-11-20 15:24:06. Others might want the UTC time to be untouched and treated as if it was stored in US/Pacific (i.e. 2017-11-20 23:24:06 is expected). Metabase can't know what the user's intent was for this implied timezone.

Having the ability to attach this timezone metadata at the column level would provide the maximum flexibility for users in this situation. In data warehouse type situations, one database might have different timezone expectations depending on which group or department's table is being queried.

There is some research and design work that will need to be done on this before it can be started.

  • Enumerate the types of fields across the databases that we support that would make use of this feature
  • Investigate the correct database dependent ways that we can shift the timezone of the stored columns (an example query for MySQL can be found in #5927)
  • Create a clear hierarchy/priority on how this column-level timezone interacts with the JVM timezone, the report timezone and the database timezone

It seems like there are three distinct pieces to this work:

  1. The ability for the user to enter timezone info for the column in the UI and that to be stored in the application database.
  2. The datatype designation of these columns as being separate from timestamp columns that already include timezone information
  3. The database driver level customization needed to translate this new timestamp type to a timezone adjusted query.
@mjalkio

This comment has been minimized.

Copy link

commented Nov 30, 2017

Hi @senior, I'm curious about what use-cases fall under "column level" timezone descriptions. In our situation, we use Metabase with a Redshift database where the datetimes are stored as TIMEZONE columns (no timezone information) and we assume stored datetimes are in UTC.

However, our Metabase users often want to make queries in a specific timezone. For example, our finance team works primarily in CST and might want to calculate transaction volume for a given quarter in CST. Meanwhile our product team tends to think in pacific time (since that's where we're located), and might want to calculate transaction volume for a day in pacific time.

From what I can tell, this type of flexibility isn't currently supported by Metabase unless you write your own SQL queries. Would what you're proposing here allow users to do what I'm describing?

@senior

This comment has been minimized.

Copy link
Member Author

commented Dec 1, 2017

@mjalkio The use case we were aiming for by associating time zone information at the column level was a data warehouse situation where the timezone isn't already associated to the column. MySQL's datetime column is an example. In that case you might have several databases from different timezones being dumped into one data warehouse.

I think the feature you are describing is interesting but different from this feature. Could you write up your feature separately? It would also be good to know what database(s) you're using and what kind of date/timestamp fields you have.

@orware

This comment has been minimized.

Copy link

commented Mar 15, 2018

I noticed this issue as well yesterday when adding a database for an application I recently built which used the MySQL Datetime type as well and am storing the values by providing a UTC value when inserting into the database.

When I created a chart regarding visits during the day I noticed that the heavy time period was showing up as 8 PM, when in reality it was more like noon (we're in Pacific Time so the offset is ~8 hours). I checked to see if there was something available to allow me to specify the timezone once I realized what was happening (Metabase was just using the datetime value as-is) but there did not appear to be any existing options for this.

@GuiSim

This comment has been minimized.

Copy link
Contributor

commented Apr 20, 2018

I encountered a similar problem today.
I have two columns in PostgreSQL, one is timestamp with time zone and another is timestamp (no timezone).

When querying, Metabase will display dates in the Metabase configured timezone for all timestamps that include the timezone. It will not assume that the timestamp without timezone is in any particular timezone.

I guess this makes sense but this creates a confusing situation where part of the dates are displayed in the Metabase configured timezone, part of the dates are displayed in another timezone.

For our use case, we don't need the per-column granularity. We'd only need to tell Metabase to always display dates in the Metabase configured timezone. If there's no timezone configured on the column, it should assume UTC (in our case).

So a global setting: " Assume UTC when no time zone is defined" would be perfect for us.

A more generic solution could be "Assume [choose timezone] when no time zone is defined".

Even more generic is a per-column solution as suggested above.

EDIT:
In the meantime, we have to convert queries to SQL and use at time zone 'UTC' on every timestamp (without time zone) column. This causes Metabase to display the dates in the Metabase configured timezone as expected.

@nskalis

This comment has been minimized.

Copy link

commented May 14, 2019

this also kind of solves the lack of timezone support in SQLite (https://metabase.com/docs/latest/troubleshooting-guide/timezones.html), would be great to have this feature; applying a function to a column.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.