1

Hello.
I am trying to filter documents by date.
I just need to select documents with a field between two dates.
Currently the only way I have found is this:

{
            "$match": {
                "acl.start": {
                    "$gte": {
                        "$humanTime": "{{ from }}"
                    },
                    "$lte": {
                        "$humanTime": "{{ to }}"
                    }
                }
            }
        }

But it’s tedious and unnecessary.
I have tried “$date”, but it seems to filter out all documents no matter what I input.
Please provide an example of hardcoding a date

1 Like
2

Unfortunately, yours is the only approach when querying Mongo. It isn’t exclusive to Redash. The $humanTime extension is useful here. But we did recently update the documentation regarding a strange edge case:

The $humanTime function is also needed when using Query Parameters of type Date or Date/Time with MongoDB, due to the difference between the format Redash uses and the one MongoDB expects.
When using a Date (or Date Range) parameter, wrap it with a $humanTime object: {{param}} becomes {"$humanTime": "{{param}} 00:00"} (the 00:00 suffix is needed only with Date parameters, for Date Time parameters you should skip it).

So a full query would look like this without aggregation:

{
  "collection": "myCollection",
  "query": {
    "date": {
      "$gte": {
        "$humanTime": "{{ from }} 00:00"
      },
      "$lte": {
        "$humanTime": "{{ to }} 00:00"
      }
    }
  }
}

Or like this with aggregation:

{
  "collection": "myCollection",
  "aggregate": [
    {
      "$match": {
        "dateField": {
          "$gte": {
            "$humanTime": "{{ from }} 00:00"
          },
          "$lte": {
            "$humanTime": "{{ to }} 00:00"
          }
        }
      }
    }
  ]
}
1 Like