Date fields are getting a time component added, should be handled in Connection TZ


#1

So I’ve read and understand https://www.metabase.com/docs/latest/troubleshooting-guide/timezones.html

We’re 100% UTC except for the Metabase client, which is set to Central time.

Here’s the problem, if you have a DATE in your MySQL database, like 2019-01-17, and you display it in your browser, Metabase will say 2019-1-16 6:00pm.

It would seem the documentation is saying “change the server timezone to central then”. I don’t think that’s a good solution. I think it’s safe to say most of us would assume when we say 2019-01-17 should be interpreted in the reader’s timezone.

Let’s say it’s Saturday 8p for me in Central time, and I ask my friend in China, “Did you go to the store today?” It’s Sunday 8am where he is. He’s not going to “where was I at today between last night at 9pm and this morning at 8am”. He’ll simply reply “No, I just woke up”.

Simply put, Metabase should not be doing converting dates to the time at midnight and substracting the offset from UTC. It should simply assume that a DATE field is always in the connection time zone.

I hope this makes sense, thank you!


#2

@jsf

Which version of Metabase and MySQL are you using?

There’s a lot of problems with timezone - it seems to be worse on MySQL than other databases.

So everything is UTC except for Metabase > Settings > Admin > General > Report Timezone ?

There are quite a few issues - open and closed - about: mysql date timezone
https://github.com/metabase/metabase/issues?q=is%3Aissue+mysql+date+timezone


#3

So we’re on:
Latest Metabase v0.31.2 Built on 2018-12-05
Latest Percona mysql version 5.7.24-27

Mea culpa, we misunderstood what that setting does. We set it to UTC and the calculations are now at least calculating correct.

BUT

Date only fields are still getting a time component added to them.


#4

Actually, I take back what I said earlier.

Date’s work as expected when the Metabase setting is set to UTC, however, this break’s DateTimes. So my initial statements are correct about how Date’s should be handled.

When you set the Metabase setting to Central, DateTimes are shifted to the local timezone which is the desired behavior, but then a Time component is appended to Date only fields and this behavior is incorrect.