Metabase Date variable calculates wrong values when selecting "previous X hours"

Bug reports
1

Describe the bug

TLDR: The "Date" filter provides wrong values in the SQL when selecting "previous 1 hour" - the time window is only 1 minute long instead of 1 hour and the time zones are wrong

(I also posted this in the github repo. I'm unsure where there or here is a more proper place)

Current situation

I have a query with a Field FIlter that is connected to a Postgres timestamptz column.
When selecting a query value of "Previous 1 hour", the resulting SQL is wrong.

Example:
My Metabase's time zone setting is set to Asia/Jerusalem (GMT+3).
Is is currently 11:40 AM in this time zone.
I have a query that has a Date filter, set to "previous hour".

The resulting SQL Metabase produces is:

...
  column_name >= timestamp with time zone '2025-05-09 07:00:00.000 +03:00'
  AND column_name < timestamp with time zone '2025-05-09 07:01:00.000 +03:00'
...

There are 2 issues with this:

  1. The resulting time window is only 1 minute long instead of 1 hour
  2. The time zone calculation is wrong

Deep dive details

These are pictures of the query I'm making and the resulting values:
Image
Image

Here's the full resulting query:
Image

Metabase version: 0.54.6 (latest)

To Reproduce

  1. Configure Metabase instance to have some non UTC time zone
  2. Create query with Date filter
  3. Choose value of "previous hour"
  4. Look at resulting query

Expected behavior

It should be one of the following two options:
Option 1:

  column_name >= timestamp with time zone '2025-05-09 07:00:00.000Z'
  AND column_name < timestamp with time zone '2025-05-09 08:00:00.000Z'

Option 2:

  column_name >= timestamp with time zone '2025-05-09 10:00:00.000 +03:00'
  AND column_name < timestamp with time zone '2025-05-09 11:00:00.000 +03:00'
  1. The difference between is >= and the < conditions is 1 hour and not 1 minute
  2. Either have a Zulu time zone and UTC time values or local time values and timezone offset, but not the original: UTC time values with timezone offset

Logs

No response

Information about your Metabase installation

{
  "browser-info": {
    "language": "en-US",
    "platform": "MacIntel",
    "userAgent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/135.0.0.0 Safari/537.36",
    "vendor": "Google Inc."
  },
  "metabase-info": {
    "databases": [
      "postgres"
    ],
    "run-mode": "prod",
    "plan-alias": "",
    "version": {
      "date": "2025-05-06",
      "tag": "v0.54.6",
      "hash": "d56bf23"
    },
    "settings": {
      "report-timezone": "Asia/Jerusalem"
    },
    "hosting-env": "unknown",
    "application-database": "h2",
    "application-database-details": {
      "database": {
        "name": "H2",
        "version": "2.1.214 (2022-06-13)"
      },
      "jdbc-driver": {
        "name": "H2 JDBC Driver",
        "version": "2.1.214 (2022-06-13)"
      }
    }
  },
  "system-info": {
    "file.encoding": "UTF-8",
    "java.runtime.name": "OpenJDK Runtime Environment",
    "java.runtime.version": "21.0.7+6-LTS",
    "java.vendor": "Eclipse Adoptium",
    "java.vendor.url": "https://adoptium.net/",
    "java.version": "21.0.7",
    "java.vm.name": "OpenJDK 64-Bit Server VM",
    "java.vm.version": "21.0.7+6-LTS",
    "os.name": "Linux",
    "os.version": "6.8.0-1023-aws",
    "user.language": "en",
    "user.timezone": "GMT"
  }
}

Severity

High - the results are extremely wrong due to the 1min instead of 1hour window

Additional context

No response