Reporting Timezone setting not working

Hello!
I have a database which stores creation timestamps in a postgres database as DATETIME with UTC timezone. I would like to show them in metabase in IST (+5:30) timezone. However, I've followed several advice from older posts as well as official documentation regarding troubleshooting timezones, and the only thing that works is explicitly modifying my SQL query and adding " at time zone 'UTC' anywhere I have to show a timestamp and setting reporting timezone in metabase settings to "Asia/Calcutta".
This approach has a few shortcomings though:

  1. Filters still don't work with the desired timezone.
  2. Anyone working on writing queries for the dashboard has to always remember to add " at time zone 'UTC'" any time they try to show a timestamp.

I would therefore like to know of a better way to achieve this.
View content in our timezone (Europe/Paris) vs UTC datas - #10 by flamber describes a similar situation as mine, and they resolved the issue by updating their database to use timestamp instead of datetime. However, I do not have the authority to make such a change on our production database. I tried to see if I could tell metabase to cast the field to a timestamp from the data model section but that too does not seem to be possible.
Is there a nice way to handle this problem?

Here's my diagnostic info:

{

"browser-info": {
"language": "en-US",
"platform": "Win32",
"userAgent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/89.0.4389.90 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": "5.3.0-1032-gcp",
"user.language": "en",
"user.timezone": "Etc/UTC"
},
"metabase-info": {
"databases": [
"h2",
"mysql",
"postgres"
],
"hosting-env": "unknown",
"application-database": "postgres",
"application-database-details": {
"database": {
"name": "PostgreSQL",
"version": "13.1"
},
"jdbc-driver": {
"name": "PostgreSQL JDBC Driver",
"version": "42.2.18"
}
},
"run-mode": "prod",
"version": {
"date": "2021-03-17",
"tag": "v0.38.2",
"branch": "release-x.38.x",
"hash": "91f0ed6"
},
"settings": {
"report-timezone": "Asia/Kolkata"
}
}
}

Hi @anamay

One workaround is to create database views, where you do the casting of your production data.

We are working on revamping how Reporting Timezone works and some of the work might land in 0.39, while other might come in later versions:
https://github.com/metabase/metabase/issues/14056 - upvote by clicking :+1: on the first post

1 Like

Thanks for the prompt response!
I think your suggestion should work, but only temporarily, until we need support for more timezones. Also appreciate all the work put into metabase product and the support :+1: