Running Metabase on AWS ElasticBeanstalk. JAVA_TIMEZONE set to US/Eastern. Time values in my postgres database are stored as UTC times.
When I create a question that computes a count of rows for “today” grouped by “hour of day”, the times still seem to be in UTC… basically they are all 4 hours ahead of the actual time in Eastern.
Changing the Metabase admin “Report Timezone” seems to have no effect as well.
Do I need to change something in the config / setup??
So I figured out my problem – my database timestamps are being stored without timezone (Rails apps do this by default and assume the times are always UTC).
I changed the column type to include timezone and now it’s working!
Hi, I face the same issue with my data, either the Timezone I set in settings, I always get raw UTC data from my query.
Can you be more explicit on the data type / column definition you use in your DB to include the timezone ? Right now I use DateTime, which seems to be timezoneless
It will really help me
You need to make sure the database column ends up as a “timestamp with time zone”.
Rails 4 by default does not convert DateTime into a timestamp with timezone on Postgres. You can override this with an initializer if you want:
ActiveRecord::ConnectionAdapters::PostgreSQLAdapter::NATIVE_DATABASE_TYPES[:datetime][:name] = “timestamp with time zone”
How about the existing
datetime data? Do I need to parse and update each field? I have the same problem where the metabase instance can only read
datetime field as UTC.
This is such a painful problem. Basically my situation is, we follow rails convention to use no timezone datetime, then when we do report in metabase, regardless how you setup the timezone I end up never able to allow user to use the timezone intended for the users.
My setup is as follows:
- JAVA_TIMEZONE: Hongkong
- Metabase Report Timezone: UTC
- PostgreSQL Timezone: UTC
- Timestamp field : No timezone specified
What I want to do is allow users to query the database in Hongkong Timezone without the need to patch the existing timestamp values, what should be the right setup?
@goodwill It would probably be better to open a new topic, since a lot has changed in the past 2.5 years
But please post your “Diagnostic Info” from Admin > Troubleshooting.
And you might want to have a look at timezone guide too: