When I have a rails postgreSQL database that use default timezone field I am unable to make the timezone result correct. I have set my report timezone as UTC (as thats what the value of the timestamp stored as) and then set JAVA_TIMEZONE as Hongkong (my actual local timezone), I am unable to get the right result I need. May I know how to solve this issue?
I know I should migrate out from h2, but does it has anything to do with my timezone issue? I have already followed all the guidelines in the timezone troubleshooting guide with no luck.
@goodwill Changing application doesn’t do anything in regards to timezone, but then you won’t end up with a corrupted database.
Without more details, it’s hard to tell what’s going on with your timezones. Perhaps you’re seeing this issue: https://github.com/metabase/metabase/issues/11819
But technically what we can do is also allow metabase to assume a time zone offset value to solve this behind the scene, why that would not be a consideration given timestamp without timezone is very common choice? Not to mention the server timezone is indeed correct- so I kinda don’t get why a solution is not proposed.
@goodwill I don’t understand. We have an issue open specific for Postgres column types timestamp - there were a massive amount of fixes for timezone in 0.34, which solved a lot of the problems that most people were having, but there’s still a couple of issues left. If you know the fix, then please submit a PR with tests
Here are the facts - all under the condition of PostgreSQL:
When you have a timestamp field (not timestamp with timezone), then in metabase, regardless of setting on Report Timezone, it will always give you the wrong result (aka GMT based result). Setting JAVA_TIMEZONE will not do anything either. I think there is just pure wrong assumption of time interpretation of timestamp without timezone.
Indeed I am surprised why database default will still act wrongly in this case as my situation is SHOW TIMEZONE will give me Hong Kong which is the right timezone. Its very clear that, again, the way how time criteria is handled is just not properly done after so many iterations.
Now I am not saying metabase is bad or anything, indeed quite the opposite, we have decided to use metabase instead of Redash because the product is just much better designed. However this timezone bug is a deal breaker for most if not all Rails users (which is actually not a small group of users), and I think the bug should be just around the time interpretation logic which hopefully is consolidated together and not too hard to be fixed. I will spend sometime to dig into it but forgive me for trying to get the contributors to help because digging into a huge codebase like metabase is not going to be an easy job to try to fix that, so if someone who has been handling the timezone issue see this question they might be able to do a much faster fix that a newbie trying to break into the code right now.
