Understanding Timezones in Metabase

Why is Metabase aware of timezones?

When a user asks to see a chart of orders by day, what do they mean by "day"?

If one order happened at '2019-01-01T12:00:00Z' and another at '2019-01-01T18:00:00Z', should they be grouped into the same day? The answer depends on what timezone the user is in. Those timestamps occurred on the same day in New York but on different days in Hong Kong.

How does Metabase handle timezones?

Metabase admins can select a "report timezone" where queries should be run. If a driver supports it, all aggregation and filtering is done in this timezone.

Regardless of driver support, the server communicates timezone information to the client. This ensures the data presentation remains consistent with query processing.

Timezones in the Data

Most databases can store timestamps with or without explicit timezone information. We recommend always storing timestamps in timezone-aware columns.

Query Processing

Our database drivers configure query timezone by setting the database session's timezone. For example, before running a query against Postgres, Metabase will execute SET SESSION TIMEZONE TO [TZ]. This ensures that values are aggregated according to date/time boundaries in the correct timezone.

Not all databases support setting a session timezone. If we're unable to run the query in the report timezone, we still need to determine what timezone the results were generated in. For example, H2 doesn't support setting a session timezone. Instead it will always aggregate values according to boundaries in the JVM timezone.

API

The API returns three pieces of timezone-related data. All three are nested within data.

{
 data: {
   rows: [
     ["2019-01-01T00:00:00.000-08:00"],
     ["2019-02-01T00:00:00.000-08:00"],
     ["2019-03-01T00:00:00.000-08:00"],
     ["2019-04-01T00:00:00.000-07:00"],
   ],
   results_timezone: "US/Pacific",
   requested_timezone: "US/Pacific",
   ...other keys omitted
}

• Requested Timezone – This is the timezone where an admin has requested queries be run. It is not always set.
• Results Timezone – This is the timezone where the query was run. It should always be set and only differ from the report timezone if the driver doesn't support timezones.
• Rows – The actual data returned from a query includes UTC offset with each ISO 8601 serialized timestamp. These should always be in the the results timezone.

Visualization

Metabase uses the query's results timezone to generate graphics and format text. This is seen when viewing charts on Daylight Savings Time boundaries. In US/Pacific, an hourly chart on March 10th 2019 will appear with 23 hourly ticks. In UTC, it will appear with 24 hourly ticks.

Warnings

There are two timezone situations that display a warning on the visualization itself. Hovering on the warning gives information about the specific timezones:

1. Unexpected Timezone

If an admin has set the report timezone but the driver was unable to run the query in that timezone.

2. Mismatched Timezones

When combining multiple queries in the same card, it's possible that the queries were run in different timezones.

Database Support

✅ Supports setting report timezone

❌ Doesn't support setting report timezone

• H2 ❌
• SQLite ❌
• MySQL ✅
• PostgreSQL ✅
• Oracle ❌
• Druid ❌
• SQL Server ❌
• Redshift ❌
• MongoDB ❌
• BigQuery ❌
• Vertica ❌
• Presto ❌
• Snowflake ❌
• SparkSQL ❌
• Google Analytics ❌

Bugs

If you're encountering issues a good first step is to read over our timezone troubleshooting guide. If you can't fix the issue, this information can help provide context to understand potential bugs.

Timezones all over

Using Metabase brings into contact many (possibly conflicting) timezones. At a high level these are the entities that have may a timezone.

• Server hosting Metabase
• JVM running Metabase
• Metabase's own database timezone
• The report timezone configured by an admin (optional and only supported for some databases)
• Databases connected to Metabase
• The user's browser accessing Metabase

Serialization between these components is another possible source of error.

Common classes of bugs

1. Conflict within the front end

Two views of the same data disagree. e.g. a bar shows 'July 4' on the axis, but 'July 5' in the tooltip

2. Front end <-> API disagreement

The API is returning a correct value to the client, but an incorrect value is rendered.

3. API is wrong

The server is producing the wrong value. Here it's especially important to isolate the different timezones that might be affecting the data. There are many misconfiguration issues that look like this type of bug.