Reporting timezone not working

Hello,
I’ve set the reporting timezone to US/Eastern (which is UTC-5) in Admin > General, but when getting the values of timestamp columns, the data is always presented in UTC (the database timezone). This causes dates to show as the next day if they are after 7pm. Our postgres database is a read-only follower and we don’t have access to change the timezone there. I am running Metabase 34.2.

Metabase is currently running in a Heroku JVM, and I’ve tried setting the timezone there setting the JAVA_TIMEZONE env var to -Duser.timezone=US/Eastern
but this has no effect. Any help is appreciated.

Thanks

Hi @line5
If I remember correctly, then having a read-only user to Postgres will also limit the option to set the session variables (specifically SET SESSION TIMEZONE TO %s;), so that means Reporting Timezone will not have much effect with that database.

Setting the -Duser.timezone=<...> parameter or the JAVA_TIMEZONE=<...> environment variable is the way to go.
Try JAVA_TIMEZONE=America/New_York

Just for reference: https://www.metabase.com/docs/latest/troubleshooting-guide/timezones.html

Thanks for the reply, but I’ve tried both of these to no avail. All reports are still showing times in UTC.

  • I’ve set the JAVA_TIMEZONE and TZ heroku env vars as America/New_York
  • In the start script, I added JAVA_OPTS+=" -Duser.timezone=America/New_York"

@line5
Please post “Diagnostic Info” from Admin > Troubleshooting.
And check the log from startup until the first query to see if there’s any timezone errors. Admin > Troubleshooting > Logs.

Diagnostic info:

{
  "browser-info": {
    "language": "en-US",
    "platform": "MacIntel",
    "userAgent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10.14; rv:72.0) Gecko/20100101 Firefox/72.0",
    "vendor": ""
  },
  "system-info": {
    "java.runtime.name": "OpenJDK Runtime Environment",
    "java.runtime.version": "1.8.0_242-heroku-b08",
    "java.vendor": "Oracle Corporation",
    "java.vendor.url": "http://java.oracle.com/",
    "java.version": "1.8.0_242-heroku",
    "java.vm.name": "OpenJDK 64-Bit Server VM",
    "java.vm.version": "25.242-b08",
    "os.name": "Linux",
    "os.version": "4.4.0-1057-aws",
    "user.language": "en",
    "user.timezone": "America/New_York"
  },
  "metabase-info": {
    "databases": [
      "h2",
      "postgres"
    ],
    "hosting-env": "heroku",
    "application-database": "postgres",
    "application-database-details": {
      "database": {
        "name": "PostgreSQL",
        "version": "10.11 (Ubuntu 10.11-1.pgdg16.04+1)"
      },
      "jdbc-driver": {
        "name": "PostgreSQL JDBC Driver",
        "version": "42.2.8"
      }
    },
    "run-mode": "prod",
    "version": {
      "tag": "v0.34.2",
      "date": "2020-02-04",
      "branch": "release-0.34.x",
      "hash": "dab738c"
    },
    "settings": {
      "report-timezone": "US/Eastern"
    }
  }
}

I just restarted the JVM dynos and here’s the log from the beginning:

[3d92ff59-0fd2-482d-b766-8838cac5a73d] 2020-02-18T16:41:44-05:00 INFO metabase.core Starting Metabase in STANDALONE mode
[3d92ff59-0fd2-482d-b766-8838cac5a73d] 2020-02-18T16:41:44-05:00 INFO metabase.server Launching Embedded Jetty Webserver with config: 
 {:port 53515}

[3d92ff59-0fd2-482d-b766-8838cac5a73d] 2020-02-18T16:41:44-05:00 INFO metabase.core Starting Metabase version v0.34.2 (dab738c release-0.34.x) ...
[3d92ff59-0fd2-482d-b766-8838cac5a73d] 2020-02-18T16:41:45-05:00 INFO metabase.core System info:
 {"java.runtime.name" "OpenJDK Runtime Environment",
 "java.runtime.version" "1.8.0_242-heroku-b08",
 "java.vendor" "Oracle Corporation",
 "java.vendor.url" "http://java.oracle.com/",
 "java.version" "1.8.0_242-heroku",
 "java.vm.name" "OpenJDK 64-Bit Server VM",
 "java.vm.version" "25.242-b08",
 "os.name" "Linux",
 "os.version" "4.4.0-1057-aws",
 "user.language" "en",
 "user.timezone" "America/New_York"}

[3d92ff59-0fd2-482d-b766-8838cac5a73d] 2020-02-18T16:41:45-05:00 INFO metabase.plugins Loading plugins in /app/plugins...
[3d92ff59-0fd2-482d-b766-8838cac5a73d] 2020-02-18T16:41:46-05:00 DEBUG metabase.plugins.lazy-loaded-driver Registering lazy loading driver :snowflake...
[3d92ff59-0fd2-482d-b766-8838cac5a73d] 2020-02-18T16:41:46-05:00 INFO metabase.driver.impl Registered abstract driver :sql  🚚
[3d92ff59-0fd2-482d-b766-8838cac5a73d] 2020-02-18T16:41:47-05:00 INFO metabase.driver.impl Registered abstract driver :sql-jdbc (parents: [:sql]) 🚚
[3d92ff59-0fd2-482d-b766-8838cac5a73d] 2020-02-18T16:41:47-05:00 INFO metabase.driver.impl Registered driver :snowflake (parents: [:sql-jdbc]) 🚚
[3d92ff59-0fd2-482d-b766-8838cac5a73d] 2020-02-18T16:41:47-05:00 INFO metabase.plugins.dependencies Metabase cannot initialize plugin Metabase Vertica Driver due to required dependencies. Metabase requires the Vertica JDBC driver in order to connect to Vertica databases, but we can't ship it as part of Metabase due to licensing restrictions. See https://metabase.com/docs/latest/administration-guide/databases/vertica.html for more details.

[3d92ff59-0fd2-482d-b766-8838cac5a73d] 2020-02-18T16:41:47-05:00 INFO metabase.plugins.dependencies Plugins with unsatisfied deps: ["Metabase Vertica Driver"]
[3d92ff59-0fd2-482d-b766-8838cac5a73d] 2020-02-18T16:41:47-05:00 INFO metabase.plugins.dependencies Metabase Vertica Driver dependency {:class com.vertica.jdbc.Driver} satisfied? false
[3d92ff59-0fd2-482d-b766-8838cac5a73d] 2020-02-18T16:41:47-05:00 DEBUG metabase.plugins.lazy-loaded-driver Registering lazy loading driver :mongo...
[3d92ff59-0fd2-482d-b766-8838cac5a73d] 2020-02-18T16:41:47-05:00 INFO metabase.driver.impl Registered driver :mongo  🚚
[3d92ff59-0fd2-482d-b766-8838cac5a73d] 2020-02-18T16:41:47-05:00 INFO metabase.plugins.dependencies Metabase Google Analytics Driver dependency {:plugin Metabase Google Drivers Shared Dependencies} satisfied? false
[3d92ff59-0fd2-482d-b766-8838cac5a73d] 2020-02-18T16:41:47-05:00 INFO metabase.plugins.dependencies Plugin 'Metabase Google Analytics Driver' depends on plugin 'Metabase Google Drivers Shared Dependencies'
[3d92ff59-0fd2-482d-b766-8838cac5a73d] 2020-02-18T16:41:47-05:00 INFO metabase.plugins.dependencies Plugins with unsatisfied deps: ["Metabase Google Analytics Driver" "Metabase Vertica Driver"]
[3d92ff59-0fd2-482d-b766-8838cac5a73d] 2020-02-18T16:41:47-05:00 DEBUG metabase.plugins.lazy-loaded-driver Registering lazy loading driver :redshift...
[3d92ff59-0fd2-482d-b766-8838cac5a73d] 2020-02-18T16:41:47-05:00 INFO metabase.driver.impl Registered driver :postgres (parents: [:sql-jdbc]) 🚚
[3d92ff59-0fd2-482d-b766-8838cac5a73d] 2020-02-18T16:41:47-05:00 INFO metabase.driver.impl Registered driver :redshift (parents: [:postgres]) 🚚
[3d92ff59-0fd2-482d-b766-8838cac5a73d] 2020-02-18T16:41:47-05:00 DEBUG metabase.plugins.lazy-loaded-driver Registering lazy loading driver :presto...
[3d92ff59-0fd2-482d-b766-8838cac5a73d] 2020-02-18T16:41:47-05:00 INFO metabase.driver.impl Registered driver :presto (parents: [:sql]) 🚚
[3d92ff59-0fd2-482d-b766-8838cac5a73d] 2020-02-18T16:41:47-05:00 INFO metabase.driver.impl Registered abstract driver :google  🚚
[3d92ff59-0fd2-482d-b766-8838cac5a73d] 2020-02-18T16:41:47-05:00 DEBUG metabase.plugins.lazy-loaded-driver Registering lazy loading driver :google...
[3d92ff59-0fd2-482d-b766-8838cac5a73d] 2020-02-18T16:41:47-05:00 INFO metabase.plugins.dependencies Metabase Google Analytics Driver dependency {:plugin Metabase Google Drivers Shared Dependencies} satisfied? true
[3d92ff59-0fd2-482d-b766-8838cac5a73d] 2020-02-18T16:41:47-05:00 DEBUG metabase.plugins.initialize Dependencies satisfied; these plugins will now be loaded: ["Metabase Google Analytics Driver"]
[3d92ff59-0fd2-482d-b766-8838cac5a73d] 2020-02-18T16:41:47-05:00 DEBUG metabase.plugins.lazy-loaded-driver Registering lazy loading driver :googleanalytics...
[3d92ff59-0fd2-482d-b766-8838cac5a73d] 2020-02-18T16:41:47-05:00 INFO metabase.driver.impl Registered driver :googleanalytics (parents: [:google]) 🚚
[3d92ff59-0fd2-482d-b766-8838cac5a73d] 2020-02-18T16:41:47-05:00 DEBUG metabase.plugins.lazy-loaded-driver Registering lazy loading driver :hive-like...
[3d92ff59-0fd2-482d-b766-8838cac5a73d] 2020-02-18T16:41:47-05:00 INFO metabase.driver.impl Registered abstract driver :hive-like (parents: [:sql-jdbc]) 🚚
[3d92ff59-0fd2-482d-b766-8838cac5a73d] 2020-02-18T16:41:47-05:00 INFO metabase.driver.impl Registered driver :sparksql (parents: [:hive-like]) 🚚
[3d92ff59-0fd2-482d-b766-8838cac5a73d] 2020-02-18T16:41:47-05:00 DEBUG metabase.plugins.lazy-loaded-driver Registering lazy loading driver :sparksql...
[3d92ff59-0fd2-482d-b766-8838cac5a73d] 2020-02-18T16:41:47-05:00 INFO metabase.driver.impl Registered driver :sqlserver (parents: [:sql-jdbc]) 🚚
[3d92ff59-0fd2-482d-b766-8838cac5a73d] 2020-02-18T16:41:47-05:00 DEBUG metabase.plugins.lazy-loaded-driver Registering lazy loading driver :sqlserver...
[3d92ff59-0fd2-482d-b766-8838cac5a73d] 2020-02-18T16:41:47-05:00 INFO metabase.plugins.dependencies Metabase cannot initialize plugin Metabase Oracle Driver due to required dependencies. Metabase requires the Oracle JDBC driver in order to connect to Oracle databases, but we can't ship it as part of Metabase due to licensing restrictions. See https://metabase.com/docs/latest/administration-guide/databases/oracle.html for more details.

[3d92ff59-0fd2-482d-b766-8838cac5a73d] 2020-02-18T16:41:47-05:00 INFO metabase.plugins.dependencies Metabase Oracle Driver dependency {:class oracle.jdbc.OracleDriver} satisfied? false
[3d92ff59-0fd2-482d-b766-8838cac5a73d] 2020-02-18T16:41:47-05:00 INFO metabase.plugins.dependencies Plugins with unsatisfied deps: ["Metabase Oracle Driver" "Metabase Vertica Driver"]
[3d92ff59-0fd2-482d-b766-8838cac5a73d] 2020-02-18T16:41:47-05:00 DEBUG metabase.plugins.lazy-loaded-driver Registering lazy loading driver :druid...
[3d92ff59-0fd2-482d-b766-8838cac5a73d] 2020-02-18T16:41:47-05:00 INFO metabase.driver.impl Registered driver :druid  🚚
[3d92ff59-0fd2-482d-b766-8838cac5a73d] 2020-02-18T16:41:47-05:00 INFO metabase.plugins.dependencies Plugin 'Metabase BigQuery Driver' depends on plugin 'Metabase Google Drivers Shared Dependencies'
[3d92ff59-0fd2-482d-b766-8838cac5a73d] 2020-02-18T16:41:47-05:00 DEBUG metabase.plugins.lazy-loaded-driver Registering lazy loading driver :bigquery...
[3d92ff59-0fd2-482d-b766-8838cac5a73d] 2020-02-18T16:41:47-05:00 INFO metabase.plugins.dependencies Metabase BigQuery Driver dependency {:plugin Metabase Google Drivers Shared Dependencies} satisfied? true
[3d92ff59-0fd2-482d-b766-8838cac5a73d] 2020-02-18T16:41:47-05:00 INFO metabase.driver.impl Registered driver :bigquery (parents: [:sql :google]) 🚚
[3d92ff59-0fd2-482d-b766-8838cac5a73d] 2020-02-18T16:41:47-05:00 DEBUG metabase.plugins.lazy-loaded-driver Registering lazy loading driver :sqlite...
[3d92ff59-0fd2-482d-b766-8838cac5a73d] 2020-02-18T16:41:47-05:00 INFO metabase.driver.impl Registered driver :sqlite (parents: [:sql-jdbc]) 🚚
[3d92ff59-0fd2-482d-b766-8838cac5a73d] 2020-02-18T16:41:47-05:00 INFO metabase.driver.impl Registered driver :h2 (parents: [:sql-jdbc]) 🚚
[3d92ff59-0fd2-482d-b766-8838cac5a73d] 2020-02-18T16:41:47-05:00 INFO metabase.driver.impl Registered driver :mysql (parents: [:sql-jdbc]) 🚚
[3d92ff59-0fd2-482d-b766-8838cac5a73d] 2020-02-18T16:41:47-05:00 INFO metabase.core Setting up and migrating Metabase DB. Please sit tight, this may take a minute...
[3d92ff59-0fd2-482d-b766-8838cac5a73d] 2020-02-18T16:41:47-05:00 INFO metabase.db Verifying postgres Database Connection ...
[3d92ff59-0fd2-482d-b766-8838cac5a73d] 2020-02-18T16:41:47-05:00 INFO metabase.driver.impl Initializing driver :sql...
[3d92ff59-0fd2-482d-b766-8838cac5a73d] 2020-02-18T16:41:47-05:00 INFO metabase.driver.impl Initializing driver :sql-jdbc...
[3d92ff59-0fd2-482d-b766-8838cac5a73d] 2020-02-18T16:41:47-05:00 INFO metabase.driver.impl Initializing driver :postgres...
[3d92ff59-0fd2-482d-b766-8838cac5a73d] 2020-02-18T16:41:47-05:00 INFO metabase.db Successfully verified PostgreSQL 10.11 (Ubuntu 10.11-1.pgdg16.04+1) application database connection. ✅
[3d92ff59-0fd2-482d-b766-8838cac5a73d] 2020-02-18T16:41:47-05:00 INFO metabase.db Running Database Migrations...
[3d92ff59-0fd2-482d-b766-8838cac5a73d] 2020-02-18T16:41:47-05:00 INFO metabase.db Setting up Liquibase...
[3d92ff59-0fd2-482d-b766-8838cac5a73d] 2020-02-18T16:41:47-05:00 INFO metabase.db Liquibase is ready.
[3d92ff59-0fd2-482d-b766-8838cac5a73d] 2020-02-18T16:41:47-05:00 INFO metabase.db.liquibase Checking if Database has unrun migrations...
[3d92ff59-0fd2-482d-b766-8838cac5a73d] 2020-02-18T16:41:51-05:00 INFO metabase.db Database Migrations Current ...  ✅
[3d92ff59-0fd2-482d-b766-8838cac5a73d] 2020-02-18T16:41:51-05:00 INFO metabase.db.migrations Running all necessary data migrations, this may take a minute.
[3d92ff59-0fd2-482d-b766-8838cac5a73d] 2020-02-18T16:41:51-05:00 INFO metabase.db.migrations Finished running data migrations.
[3d92ff59-0fd2-482d-b766-8838cac5a73d] 2020-02-18T16:41:51-05:00 INFO metabase.events Starting events listener: metabase.events.activity-feed 👂
[3d92ff59-0fd2-482d-b766-8838cac5a73d] 2020-02-18T16:41:51-05:00 INFO metabase.events Starting events listener: metabase.events.dependencies 👂
[3d92ff59-0fd2-482d-b766-8838cac5a73d] 2020-02-18T16:41:51-05:00 INFO metabase.events Starting events listener: metabase.events.driver-notifications 👂
[3d92ff59-0fd2-482d-b766-8838cac5a73d] 2020-02-18T16:41:51-05:00 INFO metabase.events Starting events listener: metabase.events.last-login 👂
[3d92ff59-0fd2-482d-b766-8838cac5a73d] 2020-02-18T16:41:51-05:00 INFO metabase.events Starting events listener: metabase.events.metabot-lifecycle 👂
[3d92ff59-0fd2-482d-b766-8838cac5a73d] 2020-02-18T16:41:51-05:00 INFO metabase.events Starting events listener: metabase.events.notifications 👂
[3d92ff59-0fd2-482d-b766-8838cac5a73d] 2020-02-18T16:41:51-05:00 INFO metabase.events Starting events listener: metabase.events.revision 👂
[3d92ff59-0fd2-482d-b766-8838cac5a73d] 2020-02-18T16:41:51-05:00 INFO metabase.events Starting events listener: metabase.events.sync-database 👂
[3d92ff59-0fd2-482d-b766-8838cac5a73d] 2020-02-18T16:41:51-05:00 INFO metabase.events Starting events listener: metabase.events.view-log 👂
[3d92ff59-0fd2-482d-b766-8838cac5a73d] 2020-02-18T16:41:51-05:00 INFO metabase.task Initializing task SyncDatabases 📆
[3d92ff59-0fd2-482d-b766-8838cac5a73d] 2020-02-18T16:41:52-05:00 INFO metabase.task Initializing task CheckForNewVersions 📆
[3d92ff59-0fd2-482d-b766-8838cac5a73d] 2020-02-18T16:41:52-05:00 INFO metabase.task Initializing task SendAnonymousUsageStats 📆
[3d92ff59-0fd2-482d-b766-8838cac5a73d] 2020-02-18T16:41:52-05:00 INFO metabase.task Initializing task SendAbandomentEmails 📆
[3d92ff59-0fd2-482d-b766-8838cac5a73d] 2020-02-18T16:41:52-05:00 INFO metabase.task Initializing task SendPulses 📆
[3d92ff59-0fd2-482d-b766-8838cac5a73d] 2020-02-18T16:41:52-05:00 INFO metabase.task Initializing task SendFollowUpEmails 📆
[3d92ff59-0fd2-482d-b766-8838cac5a73d] 2020-02-18T16:41:52-05:00 INFO metabase.task Initializing task TaskHistoryCleanup 📆
[3d92ff59-0fd2-482d-b766-8838cac5a73d] 2020-02-18T16:41:52-05:00 INFO metabase.core Metabase Initialization COMPLETE
[3d92ff59-0fd2-482d-b766-8838cac5a73d] 2020-02-18T16:41:52-05:00 WARN metabase.routes.index Locale 'en' not found.
[3d92ff59-0fd2-482d-b766-8838cac5a73d] 2020-02-18T16:41:54-05:00 DEBUG metabase.middleware.log GET /api/session/properties 200 25.6 ms (1 DB calls) Jetty threads: 4/50 (7 idle, 0 queued) (49 total active threads) Queries in flight: 0

@line5
What happens when you make a query to your Postgres data source - doesn’t it start with an error about not being able to set timezone?
I’m not sure if the problem stems from limitations of Heroku or the read-only Postgres.
I currently have access to neither, so I cannot even test right now.

When I run a query I get something like this in the logs, no errors:

[3d92ff59-0fd2-482d-b766-8838cac5a73d] 2020-02-19T10:10:34-05:00 DEBUG metabase.middleware.log POST /api/dataset 202 [ASYNC: completed] 50.0 ms (9 DB calls) Jetty threads: 2/50 (4 idle, 0 queued) (72 total active threads) Queries in flight: 0

Running SET time zone 'America/New_York' manually as a query returns no results or errors but has no effect on the time formatting. If I go directly into the database console, my queries don’t return any errors.

After some more troubleshooting, I found that we are now only having an issue with one of our tables (created_at on loan_bundles). The timestamps are all working correctly other than that one. Thanks for all the help.

@line5
Interesting. I’m not quite sure what’s going on…
Can you restart Metabase, just to make sure it’s not related to the SET ... query that you just ran?
And then have a look at this issue - specially check the comments - could it be something to do with some of your columns are timezoned while others are not?
https://github.com/metabase/metabase/issues/11819

I busted my balls on this for days.
this is what got the reporting timezone to work for me.

logged into linux terminal as root.
run this command

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql

it will prompt for your mysql root password.

This loads the timezone library into mysql.

My assumption is Metabase sends a command to change the mysql session timezone by name.
the timezone names need to be loaded or it will fail silently.

I have my server set to UTC
I have mysql set to the system (default) timezone
I have metabase set to America/Vancouver time (pacific standard time).

Now my reports show up in my local time!

Dance party :dancing_women: