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:
I have a Mongo database. I want to get all records in a range between 3 days ago and to days ago. Sounds as a common request.
I have not figured out how to make it using gui and tried to use native query but it immediately converts relative dates like “yesterday” to a fixed day like 05.01.2017.
As I cannot use js in native query and write something like “new Date()” is there any way to make relative dates possible.
I could be done with some simple convention where you replace some MetabaseDat…
And there is an open issue on Github asking something similar:
opened 11:33PM - 05 Jan 17 UTC
closed 04:45PM - 23 Apr 22 UTC
Priority:P2
Type:New Feature
Querying/Parameters & Variables
.Completeness
I have a Mongo database. I want to get all records in a range between 3 days ago… and two days ago. Sounds as a common request.
I have not figured out how to make it using gui and tried to use native query but it immediately converts relative dates like "yesterday" to a fixed day like 05.01.2017.
As I cannot use js in native query and write something like "new Date()" is there any way to make relative dates possible.
I could be done with some simple convention where you replace some MetabaseDate.daysAgo(3) to "03.01.2017" before running a query.
Looking forward to your reply, thanks for creating such a great tool.
PS or point me to the class where I could add such feature
:arrow_down: Please click the :+1: reaction instead of leaving a `+1` or `update?` comment
And a closed issue about general support for using ISODate():
opened 12:12PM - 11 Aug 16 UTC
closed 08:13PM - 13 Jan 17 UTC
Database/Mongo
.Limitation
Query fails to run: (MongoDB)
``` json
[
{
"$match": {
"hour": {
… "$gte": ISODate("2016-08-09T00:00:00Z")
}
}
},
{
"$group": {
"_id": {
"year": {"$year": "$hour"},
"month": {"$month": "$hour"}
},
"count": {
"$sum": "$count"
}
}
}
]
```
```
Unrecognized token 'ISODate': was expecting 'null', 'true', 'false' or NaN at [Source: java.io.StringReader@2b0b4b64; line: 6, column: 24]
```
It looks like ISODate() support has been added (Handle ISODate() calls in Mongo native queries [ci drivers] by camsaul · Pull Request #4132 · metabase/metabase · GitHub ), 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
kimus
November 25, 2021, 10:26pm
3
For anyone that could be searching, I'm using this (where 86400000 is 1 day):
{ $match: {
$expr: { $gte: ['$date', { $subtract: [ISODate(), 86400000] }] },
}},