We’re seeing a pesky issue that arises after UTC midnight but before midnight EST where the dates shown in the relative date filter are correct yet the SQL executes a date range that is 1 day ahead.
For example, it is currently March 5th, 8:45pm EST and setting the date filter using relative date past 30 days excluding today shows Feb 3 – Mar 4, 2026. However, when I click into the query preview it says that it’s pulling BETWEEN date '2026-02-04' AND date '2026-03-05'.
I want to pull Feb 3- March 4th (matching current timezone) using the past 30 day relative date filter. I currently have the instance report time zone set to America/New_York and am using a fully hosted Metabase version. Are there any workarounds for this?
What database type are you running the query against, and what is the data type of the column you are applying the date filter to? And you have set the ‘report timezone’ in Metabase settings to America/New_York?
And just to clarify, “fully hosted” meaning self-hosted (running on your own server) or using a paid Metabase cloud plan?
We’re running this against a PostgreSQL DB, data type of the column is “date”, and we do have the report timezone in Metabase settings set to America/New_York.
Also, yes, I did mean to say self-hosted as opposed to a paid cloud plan
What is the database time zone set to?
The database time zone is UTC, but our ETL pipeline actually converts the dates in our key tables that Metabase is querying to PST so we’re expecting PST date boundaries ie:
(p.success_date AT TIME ZONE 'America/Los_Angeles')::DATE AS payment_date
The problem here is likely the 3 different timezones in play plus the timezone-naive date field type.
You will need to make sure your predicates are set so that they translate to the desired values in the target time zone.
Metabase does all its time math using date+time with an attached time zone, which will run into problems when the time part gets truncated to compare to a date field, which are not timezone-aware in PostgreSQL. On top of that, Metabase is aware of what its “report timezone” is set to and what the database timezone is and tries to fix things so that the moments in Metabase match with how things in the database are stored.
It makes it worse if you strip the timezone off in your ETL because it effectively becomes the time in UTC, and now it’s off -7 or -8 hours depending on time of year.
Generally speaking, try to keep timezones attached to time values. There is no question at what moment a timestamp+timezone is. Don’t strip timezones if you plan on doing time math with the value, and avoid date type fields.
More info here, and click through to the linked pages, especially the Troubleshooting one.
Thanks for breaking this down!
Seems like the long-term solution for us is to backfill the table with a timezone aware column with timestamptz value instead of date
Something like DATE_TRUNC('day', p.success_date AT TIME ZONE 'America/Los_Angeles') AT TIME ZONE 'America/Los_Angeles' AS payment_date
so that we can preserve our PST date boundaries (even though our DB is still UTC). I’ll also switch the report timezone to PST as well to match.
Alternatively, changing the database timezone to PST should also work given we’ve converted most of our dates into PST already in the ETL tasks.
You can use a view to reattach the timezone to the converted values if regenerating the data is difficult.
Hi thanks for the suggestion to try the table view. I gave it a shot and recreated the date column so it has the PST timezone attached (payment_date::timestamp AT TIME ZONE 'America/Los_Angeles') AS payment_date
However, I'm still noticing that the query continues to pull in incomplete days of data after the UTC dateline using the relative date filter even though it has the timezone attached. In this example, it's currently 5:30pm PST on 3/15/26 but is still pulling in all of the 15th even though I set the filter to exclude today (the 15th):
AND "public"."daily_payment_metrics_tz"."payment_date" >= timestamp with time zone '2026-02-14 00:00:00.000 -08:00' AND "public"."daily_payment_metrics_tz"."payment_date" < timestamp with time zone '2026-03-16 00:00:00.000 -07:00'
Hm, seems to be working OK for me. How exactly did you specify the filter? I used a Fixed Date Range as follows (in my case Metabase is set to ‘database timezone’ and the database timezone is set to America/Los_Angeles):
Fixed date filter actually works fine – I see Feb 13th - 14th when specifying those dates exactly:
AND "public"."daily_payment_metrics_tz"."payment_date" >= timestamp with time zone '2026-02-13 00:00:00.000 -08:00'
AND "public"."daily_payment_metrics_tz"."payment_date" < timestamp with time zone '2026-03-15 00:00:00.000 -07:00'
Relative date using “previous 30 days excluding today” is where things start going haywire:
AND "public"."daily_payment_metrics_tz"."payment_date" >= timestamp with time zone '2026-02-14 00:00:00.000 -08:00'
AND "public"."daily_payment_metrics_tz"."payment_date" < timestamp with time zone '2026-03-16 00:00:00.000 -07:00'
Interesting, on my Metabase, it uses an interval calculation for that filter:
WHERE
(
"public"."v_accesslog_filtered"."request_time" >= CAST(
CAST((NOW() + INTERVAL '-30 day') AS date) AS timestamptz
)
)
AND (
"public"."v_accesslog_filtered"."request_time" < CAST(CAST(NOW() AS date) AS timestamptz)
)
For comparison, from a linked filter in a SQL query:
WHERE
"public"."accesslog"."request_time" >= timestamp with time zone '2026-02-13 00:00:00.000 -08:00'
AND "public"."accesslog"."request_time" < timestamp with time zone '2026-03-15 00:00:00.000 -07:00'
I suspect there is some funny math still going on somewhere, it thinks it needs to add/subtract hours and that rounds the date to the previous/next day.
Interesting, so it seems like the NOW() function the relative date filter is using is still UTC even though the column’s timezone is in PST. Is there a way to change this that’s not overriding our analytics DB’s timezone or would that be the only solution?
NOW() returns a timestamptz in the session timezone. Try running this query in a SQL question:
> select now(), current_setting('timezone'), pg_typeof(now());
now │ current_setting │ pg_typeof
───────────────────────────────┼─────────────────────┼──────────────────────────
2026-03-16 12:24:58.704271-07 │ America/Los_Angeles │ timestamp with time zone
(1 row)
Metabase will use SET TimeZone to set the session timezone to the timezone configured in Admin when it opens the session, if its set to something other than than ‘database timezone.’
Looks like it is using America/Los_Angeles then
Right. So if you want day boundaries in EST/EDT, you’ll need to express them relative to PST/PDT, i.e., 3am-3am. Or use SQL queries so you can explicitly set the timezone of the predicate and have the database do the math for you.
To be clear, I’m fine with date boundaries in PST/PDT instead of EST just not UTC. Everything looks fine right up until the UTC dateline when the relative date filter starts passing in the next day.
