Date field filter bug in native query for past X hours/minutes

We are using v1.42.3 on a redshift database. (but we have a dev environment with 1.43.3 where the same problem seems to happen)

I was trying to set up a native sql query on a table with transaction records with a field filter mapped to a datetime column, so that I can display records from the past few hours.

But it seems that whenever I try to use previous x hours or previous x minutes, it always selects records all the way back to the start of the day of the lower boundary.

Some examples to make it clearer, running the queries at around 9am UTC:

  • selecting past 24 hours will return records from the start of the previous day, (instead of only after 9am of the previous day)

-selecting previous 5 hours will return records from the start of the current day

The problem is similar with the minute resolution: Selecting previous 30 minutes for example, will return records since the start of the day

I observed this issue with both timestamp and timestamptz columns in native SQL query builder.

Note that in the custom question builder a similar setup seems to work fine.

Hi @madphysicist
You're seeing this issue:
https://github.com/metabase/metabase/issues/18315 - upvote by clicking :+1: on the first post

1 Like

After where and before {{time_filter}} you should put, a field which you want to filter.

Where Data_field > {{time_filter}}

@inzKarwowski There's a difference between Field Filters and simple filters:
https://www.metabase.com/learn/sql-questions/sql-variables.html

2 Likes

Thanks! I didn't know it.