The source of “wrong” numbers in charts or reports is often due to an underlying time zone issue. This type of issue is extremely common, both in Metabase and in many other analytics tools and services. The best way to avoid surprising time zone behavior is by selecting the “Report Time Zone” setting in the General settings tab of the Admin Panel. The Report Time Zone ensures that the time zone of query results matches the time zone used by the database for its date calculations. A Report Time Zone is currently supported on the following databases:
If you’re using a database that doesn’t support a Report Time Zone, it’s best to ensure that the Metabase instance’s time zone matches the time zone of the database. The Metabase instance’s time zone is the Java Virtual Machine’s time zone, typically set via a
-Duser.timezone<..> parameter or the
JAVA_TIMEZONE environment variable. How the time zone is set will depend on how you launch Metabase. Note that the Metabase instance’s time zone doesn’t impact any databases that use a Report Time Zone.
When you suspect a you have a time zone issue, you should collect a bit of information about your overall system.
Dec 1, 2019 00:00:00Z00is an explicitly timestamped value, but
Dec 1, 2019has an implied time zone.)
With this information collected, you can dig into the actual “mistakes” you are seeing. Most often these occur in an aggregation. It is useful to simplify the aggregation as much as you can while still seeing the “mistake.” For example, if you are looking at a “Net Negative Churn by Quarter” report that is based on an underlying table consisting of orders, see if the “count of orders by Quarter” has similarly incorrect behavior. If so, troubleshoot the second, simpler question.
Once you have simplified a question as much as possible, you can try to understand exactly what time zone conversion is causing the underlying problem. In the below, we assume that you are looking at a time series with daily values. If your error is happening with weeks, or other time granularities, perform the same sequence of steps but translating “day” to your specific granularity.
Now that you have the time zone adjustment, look at the list of time zone questions in the first set of steps and think about where this could have occurred.
For example, let’s say have a PST server time zone, and a GMT reporting time zone. If you had to manually go back 9 hours to get correct numbers, that suggests that the conversion is not happening for some reason – this suggests you are using timestamps without a time zone.
You can see a number of common problems below. If none of them apply, please open a bug report with the above information (time zones, and the results of the second troubleshooting process) as well as your Metabase, OS, and web browser versions.
You are not able to click on a cell in a result table or a chart.
We do not currently apply a reporting time zone to the results of SQL queries, so you should set one manually.
This occurs when you are grouping by a date (vs. a time) that does not have a time zone attached to it. Look at every time field your question uses in the Data Model Reference, and see if any of them are simply a “Date” field.
You will need to make sure the server time zone reflects the reporting time zone, because when a query is run on Metabase the server applies the time zone to which it is set to that date.
This often happens if you compare or perform arithmetic on two dates where one has an explicit time zone and one does not. This typically involves a question that uses multiple fields (e.g., when you filter on one timestamp and group by another). Check the time zones of each of the dates or times you are using in your question.
You will need to explicitly cast the time zone that does not have an explicit time zone. This will need to be done either in a SQL query or by transforming the data in your database to ensure both timestamps have time zones.