Any plans to support Date Range and relative dates in regular variable/filters?

I'm in the process of evaluating Metabase Cloud or Redash self-hosted to migrate our analytics out of Chartio, and one of the most frustrating things with Metabase is how it handles filters. The majority of our charts use date range and are not anchored to a column.

Another thing that doesn't make much sense is to have a field/filter called "Relative Date" and not let the user select a specific date in this same field/filter. Both Chartio and Redash have last month, last year, 30 days, and then pick a date in the same dropdown.

Is there any plans for Metabase to support filters in a more flexible way like Redash or Chartio?

Hi @danielvlopes
If the filter is not "anchored" to a column, how is the filter suppose to have any effect?
Sounds like you are looking for the following - upvote by clicking :+1: on the first post:
https://github.com/metabase/metabase/issues/4083
https://github.com/metabase/metabase/issues/4509
It's currently not on the short term roadmap, but there's constantly smaller changes arriving:
https://www.metabase.com/roadmap/

Yeah, both issues on GH are somewhat overlapping with what I'm asking. Thank you for sharing.

Re: Filter tied to columns: A filter that isn't anchored on a column could work exactly like single variables today. There's no need to require a column to have a date range or relative date.

Here's an example from Redash:

And the SQL query:

Chartio and Mode (IIRC) have the same option.

The case above I switched to two single variables, but I'm losing flexible on relative dates + single variables defaults are always fixed.

Another example of Metabase's filters being harder to work with than Chartio or Redash. The abstraction wraps so much SQL that you can't even use aliases:

A SQL using a date range like this:

select
  pfr.created_at::date response_date,
  count(*)
from
  honcho.performance_feedback_answers pfa
  inner join honcho.performance_feedback_responses pfr on pfr.id = pfa.performance_feedback_response_id
  inner join honcho.people p on pfr.author_id = p.id
where
  author_id not in ({{snippet: KYT Staff}}) and
  draft = false and
  {{ period_start }}
group by response_date

Compiles

select
  pfr.created_at :: date response_date,
  count(*)
from
  honcho.performance_feedback_answers pfa
  inner join honcho.performance_feedback_responses pfr on pfr.id = pfa.performance_feedback_response_id
  inner join honcho.people p on pfr.author_id = p.id
where
  author_id not in (
    select
      id
    from
      honcho.people
    where
      kyc_staff is true
  )
  and draft = false
  and CAST("honcho"."performance_feedback_responses"."created_at" AS date) BETWEEN ? AND ? group by response_date

Which will fail for not using the same alias (pfr vs performance_feedback_responses).

Sidenote: This is also HARD to debug since there's no place to see the compiled query (without inspecting the network responses and pulling it from the JSON).

The only solution is to:

select
  performance_feedback_responses.created_at::date response_date,
  count(*)
from
  honcho.performance_feedback_answers pfa
  inner join honcho.performance_feedback_responses on performance_feedback_responses.id = pfa.performance_feedback_response_id
  inner join honcho.people p on performance_feedback_responses.author_id = p.id
where
  author_id not in ({{snippet: KYT Staff}}) and
  draft = false and
  {{ period_start }}
group by response_date

@danielvlopes There's an issue for showing the generated SQL:
https://github.com/metabase/metabase/issues/6096 - upvote by clicking :+1: on the first post

Thank you! The issue with Generated SQL I already raised on a separate thread, and upvoted there already.

Here I'm just trying to explain how limited Metabase filters are for someone coming from Chartio/Redash (or Mode).

I have an example to get difference between dates in days using datarange variable.

1st. How to split dates? - Simple, subquering any table (logs, access, etc.) with daterange variable in WHERE clause.

SELECT count(*) AS count,
(SELECT MIN("public"."database"."crm_date") FROM "public"."database" WHERE {{date_range}}) AS "start_date",
(SELECT MAX("public"."database"."crm_date") FROM "public"."database" WHERE {{date_range}}) AS "end_date",
...
FROM
...

2nd, Using DATE_PART function to operate with the subqueries results

SELECT
...
DATE_PART('day', (SELECT MAX("public"."database"."crm_Date") FROM "public"."database" WHERE {{date_range}})::timestamp - (SELECT MIN("public"."database"."crm_date") FROM "public"."database" WHERE {{date_range}})::timestamp)) AS "Days in range",
....
FROM ...
WHERE ..
GROUP BY ...

With this subquery tip you can access to individual "date.start" and "date.end" data from data range variable in Metabase SQL.