How to extract part of a date range created using a field filter?


I’m trying to do a SQL query similar to:

WITH dates AS (
    SELECT GENERATE_SERIES({{data_cadastro_start}}::date, {{data_cadastro_end}}::date, '1 day') AS current_date
), processos AS (
    SELECT mv.status_processo_id, DATE_TRUNC('day', processo.data_cadas) AS data_cadastro FROM adm_siga.processo
    INNER JOIN adm_siga.movimento_processo AS mv ON mv.processo_id = processo.processo_id
    WHERE {{data_cadas}}
SELECT dates.current_date, 'Protocolada' AS status, COUNT(processos.status_processo_id) FROM dates
LEFT OUTER JOIN processos ON dates.current_date = processos.data_cadastro AND processos.status_processo_id = '0001'

Here {{data_cadas}} is a date range field filter. I’d like to get its start and end dates to use in the GENERATE_SERIES(). I’m using GENERATE_SERIES() to count values for all dates in the range, including dates that have no data. Is there any way to do that? Like {{data_cadas.start_range}} and {{data_cadas.end_range}} or something similar? Otherwise the only way I see to solve this is to use 2 separate variables instead of a field filter.

I'm trying to do same thing, are you able to get a workaround?

@phaetherap You will need to create a View on your database, so Metabase can reference that field, since Field Filters does not support aliasing: - upvote by clicking :+1: on the first post

Check this:

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",

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

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 ...

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