Relative date filter in native Mongo queries

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 (, 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!

1 Like

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?

1 Like

For anyone that could be searching, I'm using this (where 86400000 is 1 day):

    { $match: {
        $expr: { $gte: ['$date', { $subtract: [ISODate(), 86400000] }] },