Date Time Filter (Timezone) is not working as expected

1

I have created a report to get the total record count based on date filter through SQL Query and Question.

SQL Query shows the correct count as per the timezone configured in Metabase. However, the same report created through Question(GUI) shows incorrect count.

Database : MySQL
Data type : datetime (Dates are stored in UTC)

SQL Query (Timezone conversion)

select (convert_tz('2022-05-12 00:00:00.000', 'Asia/Kolkata', @@session.time_zone))

Output

11 May, 2022, 18:30

SQL Query (Report):

SELECT count(*) AS count from lead_summary WHERE (lead_summary.date_entered >= convert_tz('2022-05-12 00:00:00.000', 'Asia/Kolkata', @@session.time_zone)
AND lead_summary.date_entered < convert_tz('2022-05-13 00:00:00.000', 'Asia/Kolkata', @@session.time_zone))

Seems the GUI date filter is not doing the date-time conversion.

Sharing the timezone configured in metabase below:

"user.timezone": "Asia/Kolkata"
"settings": {
"report-timezone": null
}

I'm using database default timezone in metabase.

2

Hi @rathinaganesh
Post "Diagnostic Info" from Admin > Troubleshooting.
Looks like you might be seeing this issue:
https://github.com/metabase/metabase/issues/12341 - upvote by clicking :+1: on the first post

3

hi @flamber,

The filter(timezone) is not working with both datetime and timestamp field.

Below query works in SQL query

image
image1167×336 13.5 KB

Same is not working in GUI

image
image822×586 15 KB

Sharing Diagnostic Info

{
"browser-info": {
"language": "en-GB",
"platform": "Win32",
"userAgent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/101.0.4951.64 Safari/537.36",
"vendor": "Google Inc."
},
"system-info": {
"file.encoding": "UTF-8",
"java.runtime.name": "OpenJDK Runtime Environment",
"java.runtime.version": "1.8.0_312-b07",
"java.vendor": "Red Hat, Inc.",
"java.vendor.url": "https://www.redhat.com/",
"java.version": "1.8.0_312",
"java.vm.name": "OpenJDK 64-Bit Server VM",
"java.vm.version": "25.312-b07",
"os.name": "Linux",
"os.version": "3.10.0-1160.42.2.el7.x86_64",
"user.language": "en",
"user.timezone": "Asia/Kolkata"
},
"metabase-info": {
"databases": [
"mysql",
"presto-jdbc",
"h2"
],
"hosting-env": "unknown",
"application-database": "h2",
"application-database-details": {
"database": {
"name": "H2",
"version": "1.4.197 (2018-03-18)"
},
"jdbc-driver": {
"name": "H2 JDBC Driver",
"version": "1.4.197 (2018-03-18)"
}
},
"run-mode": "prod",
"version": {
"tag": "v0.43.0",
"date": "2022-05-02",
"branch": "release-x.43.x",
"hash": "ee686fc"
},
"settings": {
"report-timezone": null
}
}
}

4

@rathinaganesh You have not set Admin > Settings > Localization > Report Timezone.

MySQL does not include timezones on datetime types. Generally MySQL is tricky with timezones.
Timezones are super complicated: https://www.metabase.com/docs/latest/troubleshooting-guide/timezones.html

Guessing you're seeing a combination of 12341 and https://github.com/metabase/metabase/issues/10824.

5

Hi @flamber

Yes, Time zone is working only with timestamp field in MySQL.

any workaround for datetime field?

I'm creating report from view table

6

@rathinaganesh Convert the datetime to a timestamp, or change the timezone, when using a View.