We are having an issue where our dashboards with field date filters that are set to current day are going off of UTC time where we need them to go off of reporting timezone in the admin settings. We upgraded from 0.33.6 to 0.39.1 and since then we have been experiencing this issue. In the past we would just revert to 0.33.6 which isn't ideal and if we do so all our dashboards break now. Does anyone have a solution for this where we can stay on the more recent versions of metabase but our date field filters go off the reporting timezone and not the system/database timezone.
Our MySql version is 5.7.15
Our MySql Timezone is set to UTC (We need it to this for our own in house systems)
Our column type in MySql is date time
Our date field on our data model is set to no semantic type
Here is our diagnostic info:
{
"browser-info": {
"language": "en-US",
"platform": "MacIntel",
"userAgent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.0.4472.106 Safari/537.36",
"vendor": "Google Inc."
},
"system-info": {
"file.encoding": "UTF-8",
"java.runtime.name": "OpenJDK Runtime Environment",
"java.runtime.version": "11.0.11+9",
"java.vendor": "AdoptOpenJDK",
"java.vendor.url": "https://adoptopenjdk.net/",
"java.version": "11.0.11",
"java.vm.name": "OpenJDK 64-Bit Server VM",
"java.vm.version": "11.0.11+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.15"
},
"jdbc-driver": {
"name": "PostgreSQL JDBC Driver",
"version": "42.2.18"
}
},
"run-mode": "prod",
"version": {
"date": "2021-04-27",
"tag": "v0.39.1",
"branch": "release-x.39.x",
"hash": "6beba48"
},
"settings": {
"report-timezone": "America/Denver"
}
}
}
Here is the info we get from running these commands SELECT @@global.time_zone, @@session.time_zone:
Hi @BrettJohnson2
I have tried to reproduce your setup, but I cannot get it to fail. Can you provide exact steps-to-reproduce?
Please check the query log on MySQL to see what Metabase is sending.
Metabase applies SET @@session.time_zone = 'America/Denver' before each query, when Report Timezone is defined.
If you are using DATETIME columns, then they do not have any timezone information in them and will always displayed without any conversion. Only TIMESTAMP columns will change depending on Report Timezone.
This is similar on other database types. There's work being done to allow conversion on columns without timezone information.
So if all your columns are DATETIME, then just change Report Timezone to "Database Default".
If that doesn't work for you, then I would recommend that you revert to a backup prior to upgrading. Since you are using EB, then you are using RDS, which has automatic backups enabled by default.
We will tried changing Report Timezone to "Database Default" it does not work as intended still. We attempted to revert back to a version that is working as intended. But now all of our database model tables and dashboards receive the following error: ERROR: column "special_type" does not exist Position: 82. Is there a way around this?
@BrettJohnson2
It would be great if you could provide enough details, so this can be reproduced - otherwise I would have absolutely no idea if/when it would be fixed, and you would be stuck on 0.33.x
You are not reverting, you are downgrading. Metabase does not support downgrading. Please revert to a backup.
If you don't have a backup, then it's good time to create a backup now and make sure that you always have backups before doing any major changes (like upgrading, downgrading, etc).
And you would have to manually revert the changes done during the migration. And if you then try upgrading again in the future, then you would have to undo the manual changes you have done.
It's very easy to get the entire application database into a corrupted state, so since you're doing everything manually, then you have to remember those changes.
See this topic for details on what to change: Unknown column 'metabase_field.special_type' in 'field list'
I will provide as much as i can, i am pretty new at this so if you need any more let me know.
First some of our simpler queries are select sum(sold) from sales_view where {{saleDate}}. The sale date variable is a field filter and usually we set the default value to a relative date of today. Another thing to mention is we are using views instead of the tables themselves.
In our JBDC Additional connection string we have added useLegacyDatetimeCode=false&serverTimezone=America/Denver.
Our report timezone is set to America/Denver.
We have posted our troubleshoot diagnostic info above if that is needed.
If it helps on version 0.33.6 our date variables and date filters on dashboards were all going off of the report/timezone. Any version past that even 0.33.7 all of our variables and date filters on dashboards are going off UTC time.
If i do select now() i get Jun 23, 2021, 10:25 PM which it is Jun 23, 2021, 10:25 AM in our time. If i do select now() again after hitting the refresh button. We get Jun 23, 2021, 6:25 AM.
@BrettJohnson2 Okay, a lot to unpack.
1 - If you are using Views, then wouldn't it be possible to change the DATETIME to TIMESTAMP ?
2 - You say you have defined the Connection String - is that something new? Why is this applied?
Have you tried to not apply that - then what happens?
3 - I'm guessing that the query error does not have anything to do with the current problem? Let's try to only investigate one problem at the time. Timezones are complicated enough as-is.
4 - Are you running multiple instances? If yes, then I would understand why you're seeing different results with select now() if you have been changing the Connection String or other connection related information without rebooting all instances.
@BrettJohnson2
1 - Is the information actually stored as UTC, or are you storing it as Mountain? I understand that MySQL is running as UTC.
2 - I'm having difficulties trying to reproduce what you're seeing, but I can see difference in behavior, when I use SQL questions compared to GUI questions, and set a filter to "Today".
SQL would generate a query like this:
date(`x_formatting`.`created_at`) = '2021-06-23 00:00:00'
GUI would do this:
WHERE (`x_formatting`.`created_at` >= date(now(6)) AND `x_formatting`.`created_at` < date(date_add(now(6), INTERVAL 1 day)))
Though, the result should be the same, since SET @@session.time_zone = 'America/Denver' is sent before each query.
4 - Then that's this issue - just remember to restart the instances after changes:
https://github.com/metabase/metabase/issues/14906 - upvote by clicking 
on the first post
From what i was told the information gets stored as UTC but they are setting the timestamps of the date to 12:00:00
@BrettJohnson2 Well, I'm guessing that's the reason for the problems and why I haven't been able to reproduce - and why we haven't seen this issue being reported by anyone else.
There were extensive work in 0.34.0, which fixed more than 40 severe timezone issues. One of those changes must have impacted your specific setup, so you cannot use anything higher than 0.33.7.3 without adjusting your database columns.
That would make sense, is our best option then just to add the timezone to the time stamp like mentioned? It seems when we created the time stamp data type column. It just copied the date from date time data type column.
@BrettJohnson2 MySQL is not great at handling timezones, and because it has tried to keep backwards compatibility, then it's difficult to break free from that. In Postgres you can define if a column includes timezone information or not - that comes with it's own can of worms - timezones are really, really difficult.
I would recommend that you read through this:
https://stackoverflow.com/questions/409286/should-i-use-the-datetime-or-timestamp-data-type-in-mysql
