Cannot get timezone to work

Get Help
1

I want to convert UTC date fields to Asia/Hong_Kong timezone in the query result. But with couple hours of attempts, still cannot get it to work.

my basic info:

  1. datasource: postgresql, UTC, the date field without explicit timezone
  2. metabase JAVA_Timezone: Asia/Hong_Kong
    3 metabase report timezone: Asia/Hong_kong
  3. metabase version: 0.34.2

my diagnostic info:

{
"browser-info": {
"language": "zh-CN",
"platform": "MacIntel",
"userAgent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_13_6) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/80.0.3987.122 Safari/537.36",
"vendor": "Google Inc."
},
"system-info": {
"java.runtime.name": "OpenJDK Runtime Environment",
"java.runtime.version": "11.0.5+10",
"java.vendor": "AdoptOpenJDK",
"java.vendor.url": "https://adoptopenjdk.net/",
"java.version": "11.0.5",
"java.vm.name": "OpenJDK 64-Bit Server VM",
"java.vm.version": "11.0.5+10",
"os.name": "Linux",
"os.version": "4.15.0-66-generic",
"user.language": "en",
"user.timezone": "Asia/Hong_Kong"
},
"metabase-info": {
"databases": [
"postgres",
"h2"
],
"hosting-env": "unknown",
"application-database": "postgres",
"application-database-details": {
"database": {
"name": "PostgreSQL",
"version": "10.9 (Debian 10.9-1.pgdg90+1)"
},
"jdbc-driver": {
"name": "PostgreSQL JDBC Driver",
"version": "42.2.8"
}
},
"run-mode": "prod",
"version": {
"date": "2020-02-04",
"tag": "v0.34.2",
"branch": "release-0.34.x",
"hash": "dab738c"
},
"settings": {
"report-timezone": "Asia/Hong_Kong"
}
}
}

the thread is:

bash-5.0# ps
PID USER TIME COMMAND
1 metabase 1:04 java -XX:+IgnoreUnrecognizedVMOptions -Dfile.encoding=UTF-8 -Dlogfile.path=target/log -server -Duser.timezone=Asia/Hong_Kong -jar /app/metabase.jar

I tried:

  1. use db user that have full permissions to connect postgresql
  2. set metabase system timezone to Aisa/Hong_Kong

bash-5.0# date -R
Sun, 01 Mar 2020 17:51:58 +0800

I didn't see any error in logs, but I still get UTC value.

How do I get it work?

PS. I run metabase on docker, and also use postgresql as metabase applicaiton db. But I didn't feel that this could be a problem.

2

Hi @chen2liang4
Are you using columns in Postgres with or without timezones?
That will make a difference - have a look in the comments of this issue:
https://github.com/metabase/metabase/issues/11819
Just for reference: https://www.metabase.com/docs/latest/troubleshooting-guide/timezones.html

3

@flamber, it's the timestamp without timezone.
I thought that it will be converted by the report timezone setting.

According to https://www.metabase.com/docs/latest/troubleshooting-guide/timezones.html

We do not currently apply a reporting time zone to the results of SQL queries, so you should set one manually.

In my case, simple query doesn't work too.

4

@chen2liang4 Is the Postgres user permitted to set session variables? That’s how Reporting Timezone is applied on Postgres.
But you should see a little log error about not being able to apply timezone in Admin > Troubleshooting. Check from startup until you make the first query.

5

@flamber, I use a super user to connect Postgres. I didn’t see any log error.
I trid to run metabase latest version, 0.34.3
java -Duser.timezone=Asia/Hong_Kong -jar metabase.jar
still get UTC date time.

6

@chen2liang4 Can you provide a sample table of your schema, just so I can try to reproduce this - I’m not sure if it’s a new issue (since 0.34.0 fixed a lot of timezone issues) or an existing issue that I need to find.

7

@flamber, here are steps to reproduce

  1. create a table and insert sample data in postgresql 10.
CREATE TABLE timestamp_demo (id int4 , ts TIMESTAMP , tstz TIMESTAMPTZ );
insert into timestamp_demo values(1, '2020-02-29 12:00:00', '2020-02-29 12:00:00+00');
  1. run metabase
java -Duser.timezone=Asia/Hong_Kong -jar metabase.jar
  1. set report timezone to Asia/Hong_Kong in admin panel.
  2. create a simple query question and select timestamp_demo table.
    expect result
id |         ts          |             tstz
----+---------------------+-------------------------------
  1 | February 29, 2020, 8:00 PM | February 29, 2020, 8:00 PM

actual result

id |         ts          |             tstz
----+---------------------+-------------------------------
  1 | February 29, 2020, 12:00 PM | February 29, 2020, 8:00 PM

I hope that ts column has save value as tstz column.

8

@chen2liang4 I’m almost certain that you’re seeing https://github.com/metabase/metabase/issues/11819, which I already linked to - it looks very similar to the example posted in the comments.
I would recommend that you comment there - also read the link in the last comment about Postgres timezone handling.

9

@flamber, I had commented on that open issue, thanks.