Dynamic date range filters in PostgreSQL

[szaballa]

Hello everyone!
We are implementing Looker with a warehouse in PostgreSQL, we have some Facts partitioned by date at a monthly level.
The issue is that Looker explorer, when using dynamic filters, performs a subquery within the where, for example. "dt_fecha" < (SELECT DATE_TRUNC('day', CURRENT_TIMESTAMP AT TIME ZONE 'America/Argentina/Buenos_Aires')) causing it to not use partitioning since it must resolve the subquery.
If I copy the query and execute it in the database, the same thing happens, but if I remove only the word "SELECT" from the subquery, it no longer has to resolve the subquery, but only resolves the date function and if it uses the partitions.
The truth is that Looker's built-in functions for processing dates are very practical, but I CANNOT use them.
I searched to see if I could put something together with LookerML but I didn't find anything.
Any ideas?
Thank you!

[jswett]

How is `dt_fecha` modeled in Looker and how was it partitioned in DB (that is, the DDL for the table)?

[jswett]

Also, what does `EXPLAIN (ANALYZE, VERBOSE) ` yield between the two queries?

[szaballa · Accepted solution]

"dt_date" is modeled in Looker as a dimension of the view that represents a fact table in my warehouse and is the attribute of said fact table by which the table is partitioned in the database.
My way of solving it after reading many posts in this Forum was to create a view based on a derived table where I apply the date filter, which is the attribute of the database partition with the date_start and date_end variables and the definition of a filter_date.
It is not the best solution but it worked for me in postgresql, even though I applied the same filter twice, but the first is the one that allows the partition to be used and the second, when armed by looker (with the SELECT), applies it at the end, but the result is the same data set as the first filter.

This forces the creation of the SQL to generate a WITH with the filter as needed by PostgreSQL to use the partitioning and on this query it applies the filter again but as created by the Looker tool, which to calculate each relative date applies a subquery (SELECT) the which does not work correctly for the use of partitioning.
Thank you very much jswett for your interest.