Date range filter for custom field

Get Help
1

Hi! I was trying to do some questions with metabase, but struggle to figure out how to properly add query for calculated field. I need to select entities, that differ from date range for 2 weeks (interval '2 week' in PG), but I cant add any calulations to query. I tried two different ways:

  1. Add custom field, but it does not support intervals in definitions, so something like this does not work
= [Users.created_at] + interval '2 week'
  1. Second approach was creating SQL question, but problem is recommended way is to select field in the variable definition like and I cant add interval into this condition, but general recommendation is to keep conditions column names in the variable.
[[where {{date_dange}}]]

Is there are any way to solve this problem? Or only way is to add 2 variables and prohibiting rule of keeping field name inside variable definition?

2

Hi @arrowcircle
Custom Expressions currently doesn't handle dates:
https://github.com/metabase/metabase/issues/11330 - upvote by clicking :+1: on the first post

If you always need that, then you can make a View on the database.

Or do some sub-select hackery, when using Field Filters. Example (haven't tested):

SELECT *
FROM
(
  SELECT max(created_at) as min_ca, min(created_at) as max_ca
  FROM users
  [[WHERE {{date_range}}]]
) as hacky, users
WHERE 1=1
AND users.created_at BETWEEN hacky.min_ca AND hacky.max_ca

Or use two simple Date filters, which allows you to do manipulation on the variable.
https://www.metabase.com/learn/building-analytics/sql-templates/sql-variables.html