Relative date filters for Native SQL queries


I am using Metabase version v0.29.3 .
I am working on MYSQL database.
I have a “timestamp” column in one of the tables.
The column type is UNIX Timestamp (Seconds).

When creating a question from a “Custom” table view, we are able to use the “Relative Date” filter for “timestamp”.
However, when we use the “Native Query” for SQL, we get zero results.

Sample Query :
select count(*) from table_name where {{relative_date}} .

Here, we use field filter option for “relative_date” variable.
The column selected is “timestamp” from table “table_name”.
The “timestamp” column contains epoch timestamp .

Please help !!

As far as I know, it’s not possible to use relative date filters on “Native Query” questions. I believe this is due to technical limitations behind the query builder that Metabase uses. When you use the query builder UI, Metabase can generate a correct SQL query from the relative date widget, but it can’t make the same changes to a native SQL query without potentially affecting the query.

Not a Metabase developer, so take this with a grain of salt. All I know is that I haven’t been able to get relative date filters to work with my Native SQL queries either. I primarily use SQL Server however.

I am not sure how elaborate your “relative date” has to be, but I could get a similar result with the following:

select count(*) from “my_table” where “my_date” > (current_date - interval ‘2 week’) and “my_second_date” < (current_date - interval ‘2 week’);

Other than “week”, you can use any interval of time, singular, with the desired number, like:
(current_date - interval ‘4 day’)
(current_date - interval ‘7 month’)

This was of course use as input in the native query.