Field filtering on a timestamp with timezone only reporting in UTC

Our database is in UTC, but we operate in PST. All of the times work correctly if I do AT TIME ZONE ‘america/los_angeles’ when doing my queries, however I really like to use the field filters and so do my coworkers. For the past few weeks we’ve been having to deal with what I thought was a database issue on our side, however it seems like the field filters are the cause. Basically, when using them to say filter on June 1st, the day will ‘start’ at 5pm PST. See below:

image

However, when writing out the WHERE statement myself, I can achieve the correct time. See below: (can only post one image apparently)

Is there anyway to apply a time zone to datetime field filters?

2 Likes

Hi @zsmith2
Please post “Diagnostic Info” from Admin > Troubleshooting.
And which database are you querying?

I would guess that you’re having the same behavior using the filters in Simple/Custom question, so it’s not specific to Field Filter?

While a lot of timezone issues were fixed in 0.34, there are still a few left:
https://github.com/metabase/metabase/issues?q=is%3Aopen+is%3Aissue+label%3AMisc%2FTimezones+filter

I’m having exact the same problem, I would like to change the timezone of my datetime field filters.
Is this possible in any way?

@Cappie2707
Please post “Diagnostic Info” from Admin > Troubleshooting.
And which database are you querying?

Don’t know if I completely caught your problem, but I just faced something similar today. I solved by checking the actual executed query on postgres and found that it expands to

CAST("schema"."table"."column" AS date) = $1

and, of course, uses the driver/pg to expand $1 to the set value. It is actually a simple string replacement so you can add a the time zone statement right after like this:

{{filterName}} AT TIME ZONE 'your time zone'

The solution is unfortunately strongly coupled with the Metabase implementation, but solves the problem anyway :slight_smile: .

Hope it helps

Regards,
Heitor

1 Like