Timezone setup issue?

1

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??

Thanks :slight_smile:

2

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! :slight_smile:

3

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 :confused:

It will really help me :slight_smile:

Thank you!

4

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”

5

Hi @key88sf

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.

Thanks!

6

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:

  1. JAVA_TIMEZONE: Hongkong
  2. Metabase Report Timezone: UTC
  3. PostgreSQL Timezone: UTC
  4. 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?

7

@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:
https://www.metabase.com/docs/latest/troubleshooting-guide/timezones.html