When we update metabase to v0.38.0, all of our dashboards that are set to today, change to tomorrow at 5pm mst. We have our metabase instance timezone set up for MST time but this still happens. Is there something we are missing or a way to fix this?
Hi @BrettJohnson2
Please post “Diagnostic Info” from Admin > Troubleshooting.
Which version did you upgrade from?
I’m not sure I understand what you mean by “change to tomorrow at 5pm mst” - do you mean the results, or a filter, or …?
{
“browser-info”: {
“language”: “en-US”,
“platform”: “MacIntel”,
“userAgent”: “Mozilla/5.0 (Macintosh; Intel Mac OS X 11_2_1) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/88.0.4324.182 Safari/537.36”,
“vendor”: “Google Inc.”
},
“system-info”: {
“file.encoding”: “UTF-8”,
“java.runtime.name”: “OpenJDK Runtime Environment”,
“java.runtime.version”: “11.0.10+9”,
“java.vendor”: “AdoptOpenJDK”,
“java.vendor.url”: “https://adoptopenjdk.net/”,
“java.version”: “11.0.10”,
“java.vm.name”: “OpenJDK 64-Bit Server VM”,
“java.vm.version”: “11.0.10+9”,
“os.name”: “Linux”,
“os.version”: “4.14.62-65.117.amzn1.x86_64”,
“user.language”: “en”,
“user.timezone”: “GMT”
},
“metabase-info”: {
“databases”: [
“mysql”
],
“hosting-env”: “elastic-beanstalk”,
“application-database”: “postgres”,
“application-database-details”: {
“database”: {
“name”: “PostgreSQL”,
“version”: “10.13”
},
“jdbc-driver”: {
“name”: “PostgreSQL JDBC Driver”,
“version”: “42.2.18”
}
},
“run-mode”: “prod”,
“version”: {
“date”: “2021-02-16”,
“tag”: “v0.38.0”,
“branch”: “release-x.38.x”,
“hash”: “471aa51”
},
“settings”: {
“report-timezone”: “US/Mountain”
}
}
}
what is happening is all of our dashboards by default are filtered for today. As soon as 5pm hits mst our dashboards go blanks because the today filter acts like it is tomorrow. We upgraded from V0.33.6 i believe. This usually happens though everytime we upgrade so we roll back.
@BrettJohnson2 Timezone are a pesky thing.
https://www.metabase.com/docs/latest/troubleshooting-guide/timezones.html
Which version of MySQL
What’s the timezone of MySQL?
And which column type in MySQL is the filtered on the dashboard referenced to?
I know there’s a problem on Postgres, when the columns doesn’t contain timezone information, then Metabase will treat them with the instance timezone (which is set to GMT, since you’re likely using Docker).
@flamber sorry for the delayed response, it seems that the date fields in our database are correct. When we run a query to check the dates they come back in the correct format/timezone. We are suspecting that the issue comes from the dashboard relative date filter or the date variables in our queries. The column type though for the dates has no special type. And the timezone of our MySQL and Elastic beanstalk are the defaulted utc time. We would like to get this resolved without having to change the timezone of our beanstalk/database timezone. We also find it very weird that if we role back to version 0.33.6 all dashboards work as intended. But any version from there we upgrade we run into this issue.
We suspect it’s the dashboard relative date filters, as well as the variables in our SQL queries. Because they seem to still be going off of gmt timezone and not the timezone we select in the localization settings of the admin portal.
@BrettJohnson2
Which version of MySQL
And which actual database column type in MySQL is the filtered on the dashboard referenced to?
Check the request made on the dashboard in your browser developer Network-tab and look in the response to see the query used.
It sounds a lot similar to the Postgres issue:
https://github.com/metabase/metabase/issues/11819
The version of MySQL is 5.7 i believe, and the column type in the view we created is datetime. We are also changing the timezone of the dates to the appropriate timezone in our view. When we check the network-tab we see ERROR metabase.driver.sql-jdbc.execute Failed to set timezone:
@BrettJohnson2 Admin > Troubleshooting > Logs are where you can see more information. Sounds like you might not have loaded your MySQL with tz_data.
that is the error we see in the logs ERROR metabase.driver.sql-jdbc.execute Failed to set timezone:
Assert failed: (re-matches #"[A-Za-z/_]+" <>). But why would this be an issue when updating from 0.33.6 to 0.38.0. While on 0.33.6 we have zero issues.
Shouldn’t the date filters on dashboards just reflect whatever timezone is selected on the admin > localization drop down?
@BrettJohnson2 There were about 40 timezone issues fixed in 0.34 on various databases. And more in later versions.
Without knowing which version of MySQL you’re using and if it has tz_data, and a sample schema to reproduce, then it’s hard to tell.
Timezone problems are some of the most difficult issues to fix and we’re working on completely changing how timezones are handled (perhaps 0.39):
https://github.com/metabase/metabase/issues/14056
I think from that link by separating the time zones into display/filter time zones is probably what we are looking for so maybe we will wait to update till that is released. But here is mySQL version Ver 14.14 Distrib 5.7.15. and our datetime fields look like this 2021-03-01 12:00:00 (we are converting the dates to 12:00:00). We have also tried CONVERT_TZ (‘2021-03-01 12:00:00’, ‘+00:00’,’-07:00’) to change our date times. As mentioned before we might as well just wait for 0.39 and stay on 0.33.6 for now. We do appreciate the help.