PostgreSQL: Timezone not properly handled under timestamp without timezone with correct database timezone setup

[goodwill]

Describe the bug
In postgresql database with correct timezone set (SHOW TIMEZONE showing the intended timezone of the report), there is no way to properly set the report to run with the right timezone. Setting Report Timezone to the same timezone of the database does not help, it just all being interpreted as GMT.

Logs

{
  "browser-info": {
    "language": "en",
    "platform": "MacIntel",
    "userAgent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_6) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/85.0.4183.121 Safari/537.36",
    "vendor": "Google Inc."
  },
  "system-info": {
    "file.encoding": "UTF-8",
    "java.runtime.name": "OpenJDK Runtime Environment",
    "java.runtime.version": "11.0.8+10",
    "java.vendor": "AdoptOpenJDK",
    "java.vendor.url": "https://adoptopenjdk.net/",
    "java.version": "11.0.8",
    "java.vm.name": "OpenJDK 64-Bit Server VM",
    "java.vm.version": "11.0.8+10",
    "os.name": "Linux",
    "os.version": "4.15.0-1083-azure",
    "user.language": "en",
    "user.timezone": "Hongkong"
  },
  "metabase-info": {
    "databases": [
      "postgres"
    ],
    "hosting-env": "unknown",
    "application-database": "h2",
    "application-database-details": {
      "database": {
        "name": "H2",
        "version": "1.4.197 (2018-03-18)"
      },
      "jdbc-driver": {
        "name": "H2 JDBC Driver",
        "version": "1.4.197 (2018-03-18)"
      }
    },
    "run-mode": "prod",
    "version": {
      "date": "2020-07-31",
      "tag": "v0.36.2",
      "branch": "release-0.36.x",
      "hash": "13f0225"
    },
    "settings": {
      "report-timezone": "Asia/Hong_Kong"
    }
  }
}

To Reproduce

1. Run SQL Query SHOW TIMEZONE, confirm Timezone shows as Hong Kong;
2. JAVA_TIMEZONE set to Hongkong
3. Select any table with timestamp field using simple query
4. Use the timestamp field to query Previous 1 day
5. The result shown will be using GMT, completely ignore the timezone configuration

Expected behavior
Suggest timestamp without timezone should be interpreted as GMT always, then use the report timezone (even Database Default should be using SHOW TIMEZONE as a reference) to adjust the time of the report. Alternatively allow people to setup the database timezone for timestamp without timezone if we want this to be super flexible.

Information about your Metabase Installation:
Browser agnostic. Metabase Version 0.36.2 Community hosted inside docker

Severity
High - basically breaks all queries unless I build dataview to convert the timestamp. Also please be aware this is default behaviour for Ruby on Rails database, so meaning anyone use Ruby on Rails based DB on PostgreSQL are facing the same issue.

[flamber]

Related to #11819

[goodwill]

I am not entirely sure about this, as in that other ticket the issue only happens with today, while in my case it just always happens regardless of date range you use.

Also I guess the more important issue here is to come to conclusion on what is the right approach to solve this issue?

[flamber]

Perhaps more correctly #6259/#3914
The workaround is the same as I noted in the forum - create a database view with conversion, so the columns has timezone.