Time Zone for date display

1

Hi, my application database is mysql. There are quite a few date fields. All the dates are stored in UTC format. There is one field which is in datetime format. So for example date stored is February 7, 2021, 9:30 PM. When converted to IST (+5:30) it Date should be February 8, 2021, 15:00 AM. How do it ensure this? I changed the report settings to IST but still query results show UTC format. Here is the diagnostic information:

{
“browser-info”: {
“language”: “en-US”,
“platform”: “Win32”,
“userAgent”: “Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:84.0) Gecko/20100101 Firefox/84.0”,
“vendor”: “”
},
“system-info”: {
“file.encoding”: “UTF-8”,
“java.runtime.name”: “OpenJDK Runtime Environment”,
“java.runtime.version”: “11.0.9.1+1-Ubuntu-0ubuntu1.18.04”,
“java.vendor”: “Ubuntu”,
“java.vendor.url”: “https://ubuntu.com/”,
“java.version”: “11.0.9.1”,
“java.vm.name”: “OpenJDK 64-Bit Server VM”,
“java.vm.version”: “11.0.9.1+1-Ubuntu-0ubuntu1.18.04”,
“os.name”: “Linux”,
“os.version”: “5.4.0-1029-aws”,
“user.language”: “en”,
“user.timezone”: “Etc/UTC”
},
“metabase-info”: {
“databases”: [
“mysql”
],
“hosting-env”: “unknown”,
“application-database”: “mysql”,
“application-database-details”: {
“database”: {
“name”: “MySQL”,
“version”: “5.7.32-0ubuntu0.18.04.1”
},
“jdbc-driver”: {
“name”: “MariaDB Connector/J”,
“version”: “2.5.1”
}
},
“run-mode”: “prod”,
“version”: {
“date”: “2020-05-28”,
“tag”: “v0.35.4”,
“branch”: “release-0.35.x”,
“hash”: “b3080fa”
},
“settings”: {
“report-timezone”: “Asia/Calcutta”
}
}
}

2

Hi @jiwnaiakbar
I don’t understand. MySQL does not store timezone information in datetime columns - only timestamp:
https://stackoverflow.com/questions/19843203/how-to-store-a-datetime-in-mysql-with-timezone-info

Timezones are some of the most complicated parts of Metabase (and data storage in general), so it’s important to understand every single part of your setup to being able to reproduce and fix potential problems:
https://www.metabase.com/docs/latest/troubleshooting-guide/timezones.html

Latest release is 0.37.7

3

Hi @flamber. Apologies for not being clear in my question. I have a column name Start_Date which is of type Datetime. Date stored in this table is in UTC format. When I do a select Start_date from Table; I was hoping that it would fetch this date and show it to me by converting into reporting timezone format.

4

@jiwnaiakbar Metabase does not convert non-tz columns currently, so you would need to either change the column or create a database View.

5

Ok. Thnx

6

@jiwnaiakbar So, these are very technical descriptions, but when they are fixed, it would make it possible to do such conversion in Metabase instead of needing a View - upvote by clicking :+1: on the first post of each issue:
https://github.com/metabase/metabase/issues/14054
https://github.com/metabase/metabase/issues/14056

1 Like
7

Hi both !
@flamber
We are using MySQL and Ruby on Rails - which ONLY stores as DATETIME - and stores in UTC (and converts in the app to AEST or AEDST as we are on now)
We are UTC+11 - so big difference to UTC
unless we use custom SQL - or maybe a View as our recommend - the date filters select data only in UTC…
would these items allow us to address our issue ??
any other suggestions ?
thx
Neil

8

Hi Neil, I created a view with the additional adjusted date column. It works.
One more thing I have done is I have removed report timezone setting as it gets confusing. Some queries like select now() will bring UTC adjusted while other datetime will not so if you are calculating time difference then it brings incorrect results. So as a thumbrule, all date fields we have added adjusted UTC date column using view and use it across and disabled report time zone setting