View content in our timezone (Europe/Paris) vs UTC datas

Get Help
1

Hi team,

We are storing our datas into UTC timezone, but as we are in Paris, when we are checking a “Today dashboard”, we miss 2 hours :confused:

Is there any way to solve that ? We know that we could write our own SQL queries to handle timezones, but it’s pretty annoying to get a such easy tool and to be force to query database by manual queries.

Many thanks for your help

2

If it can help, we’re using Heroku install of Metabase v0.35.1, with Aurora MySQL, and our database get SYSTEM on “SELECT @@global.time_zone;” and “2020-04-11 19:28:52” ON “SELECT NOW();” (-2 hours)

3

Hi @monisnap-jonathan
It would help if you posted “Diagnostic Info” from Admin > Troubleshooting.
You might want to have a look at the troubleshooting guide:
https://www.metabase.com/docs/latest/troubleshooting-guide/timezones.html
I don’t think Heroku can understand the environment variable JAVA_TIMEZONE, so it needs to be defined some other way, if needed.

4

Thanks for your answer @flamber

We’re storing dates with DATETIME MySQL data type.

Please find below our “Diagnostic Info” :

{
  "browser-info": {
"language": "en",
"platform": "MacIntel",
"userAgent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_3) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/80.0.3987.163 Safari/537.36",
"vendor": "Google Inc."
  },
  "system-info": {
"file.encoding": "UTF-8",
"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-1062-aws",
"user.language": "en",
"user.timezone": "Etc/UTC"
  },
  "metabase-info": {
"databases": [
  "mysql",
  "h2"
],
"hosting-env": "heroku",
"application-database": "postgres",
"application-database-details": {
  "database": {
    "name": "PostgreSQL",
    "version": "12.2 (Ubuntu 12.2-2.pgdg16.04+1)"
  },
  "jdbc-driver": {
    "name": "PostgreSQL JDBC Driver",
    "version": "42.2.8"
  }
},
"run-mode": "prod",
"version": {
  "tag": "v0.35.1",
  "date": "2020-04-02",
  "branch": "release-0.35.x",
  "hash": "e67f169"
},
"settings": {
  "report-timezone": "UTC"
}
  }
}

Is there any settings to tell Metabase, to “convert” automatically the dates read as UTC+2 ?

5

I tried the following :

  • Define the database timezone, which was SYSTEM, by :
    SET @@global.time_zone = “+00:00”;
    SET @@session.time_zone = “+00:00”;

AND SELECT @@global.time_zone, @@session.time_zone; now returns
SYSTEM +00:00

And then into Metabase settings, I put an other timezone (ie : Europe/Berlin, because I don’t find Europe/Paris), but my reports are still UTC :frowning:

6

@monisnap-jonathan
Setting Admin > Settings > General > Report Timezone to “Europe/Berlin” should make Metabase set the session timezone.
https://github.com/metabase/metabase/blob/master/src/metabase/driver/mysql.clj#L307-L315
But maybe you haven’t loaded timezones into MySQL? In that case, then you should see an error/warning in the Metabase log. Check from startup to first time you make a query. Admin > Troubleshooting > Logs.

7

I just ran a query and get

[3952bb0b-2bee-4dd9-b5ba-b8e720f0dc9c] 2020-04-12T12:55:54+02:00 ERROR metabase.sync.util Erreur à la génération de lempreinte pour {0}
[3952bb0b-2bee-4dd9-b5ba-b8e720f0dc9c] 2020-04-12T12:55:58+02:00 DEBUG metabase.middleware.log POST /api/dataset 202 [ASYNC: completed] 4,8 s (5 DB calls) App DB connections: 1/13 Jetty threads: 4/50 (1 idle, 0 queued) (52 total active threads) Queries in flight: 0 (0 queued)
[3952bb0b-2bee-4dd9-b5ba-b8e720f0dc9c] 2020-04-12T12:56:01+02:00 DEBUG metabase.middleware.log GET /api/user/current 200 11,3 ms (3 DB calls) App DB connections: 0/13 Jetty threads: 6/50 (1 idle, 0 queued) (52 total active threads) Queries in flight: 0 (0 queued)
[3952bb0b-2bee-4dd9-b5ba-b8e720f0dc9c] 2020-04-12T12:56:01+02:00 DEBUG metabase.middleware.log GET /api/session/properties 200 15,0 ms (2 DB calls) App DB connections: 0/13 Jetty threads: 5/50 (1 idle, 0 queued) (52 total active threads) Queries in flight: 0 (0 queued)
[3952bb0b-2bee-4dd9-b5ba-b8e720f0dc9c] 2020-04-12T12:56:01+02:00 DEBUG metabase.middleware.log GET /api/database 200 8,7 ms (3 DB calls) App DB connections: 0/13 Jetty threads: 5/50 (1 idle, 0 queued) (52 total active threads) Queries in flight: 0 (0 queued)

Unfortunately I do not see any error due to date.
Does my issue come from the fact we're storing date as DATETIME MySQL type ?

8

@monisnap-jonathan
It would be helpful with a sample table, so I can try to reproduce this.
The “Diagnostic Info” you provided earlier says that you’re using “UTC” as Report Timezone, and not “Europe/Berlin” - did you change something between the two comments ?
Have you tried setting Admin > Settings > General > Report Timezone to “Database default” ?

9

I think I found the solution.

Dates are stored on DATETIME format. I tried to duplicate one of my table, and update type per TIMESTAMP.
Dates stored stay the sames, by dates displayed on Metabase use the timezone set into settings !

:slight_smile:

Do you think that could make sens and explain the issue ?

10

@monisnap-jonathan

I’m not sure which version of MySQL you’re using:
https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Updates.html

I guess it’s a combination of Aurora, Heroku and DATETIME for a date, that makes some bad combination, but without a lot more details on what the various requests are returning, it’s difficult to know.

On Postgres you can have TIMESTAMP AND TIMESTAMPTZ, which can also cause some confusion, so yes, choosing a different column can make Metabase return a different result.

11

Many thanks.

TIMESTAMP solve the situtation ! By declaring TIMESTAMP instead of DATETIME data types, Metabase is able to use the sessions timezones :slight_smile: