Relative date filter in native Mongo queries


#1

MongoDb is our primary database, and we have a changelog collection for building an activity feed. The collection has a date field. What we want is a query that grabs items from the last 7 days. This is easily possible using the visual editor/GUI to build the query, as we just add a filter for “Previous 7 days (including today)”. However, we need to do some additional processing within the query (including a join to a different collection), so we want to do a native Mongo query using aggregation pipeline.

When I create the “Previous 7 days” query in the visual editor and click “View the native query”, it looks like the $match criteria gets hardcoded dates, so we wouldn’t be able to use the generated native query on an ongoing basis:

....
"$match": {
  "changeDate~~~day": {
    "$gte": {"___date":"2018-03-15"},
    "$lte": {"___date":"2018-03-22"}
  }
}
...

Ideally, we’d be able to do something like this instead:

[
  {
    "$match": {
      "changeDate": { "$gte": new Date(new Date() - 1000 * 60 * 60 * 24 * 7) }
    }
  },
  // ...
]

It looks like someone asked a very similar question over a year ago but hasn’t gotten a response yet:

And there is an open issue on Github asking something similar:

And a closed issue about general support for using ISODate():

It looks like ISODate() support has been added (https://github.com/metabase/metabase/pull/4132), but I still haven’t seen an example of a dynamic date filter in a native Mongo query, and none of my tests have been successful thus far.

Has anyone else run into this issue or know a way around it? Would absolutely love to see a code example if you have! Thanks!


#2

I noticed the same, we have Unix timestamps which are set as date in the data models. It’s displayed correctly, however on dashboard relative date filter does nothing.

Am I missing something here? Do you need to specify date field used for relative date filter?