CODEWITHSUNDEEP
mongodbaggregate
alireza arvandi
alireza arvandi

Reputation: 43

mongodb aggregate get current date

I'm using metabase and I have native mongodb query. I want to filter documents created yesterday. The problem is that I only have json. Is there any way to compute yesterday?

my json is:

[...
{
    "$match": {
      "createdAt": { "$ne": "$yesterday" },
    }
},
...]

Upvotes: 3

Views: 5926

Answers (4)

Rizwan Amjad
Rizwan Amjad

Reputation: 442

You can use dateSubtract function to get current date - n days

{
    "createdAt": {
        $lt: {
              $dateSubtract: {
                  startDate: { $toDate: "$$NOW" }, // Current date
                  unit: "day",
                  amount: 1,
              },
        },
    },
},

Upvotes: 0

Jerry
Jerry

Reputation: 1

pay_status is a field of current table. After map them,then you can choose.

[       
{$match:{{pay_status}}},
{$match:{{pay_time}}},
{$project:{...}}
]

Upvotes: 0

Fateme Ahmadi
Fateme Ahmadi

Reputation: 344

Unfortunately Metabase does not allow to use Date() to get now Date. also you should notice that dateFromString is available in version 3.6 of mongoDB and newer but another problem here is i think dateFromString does not work well with now Date in aggregate and mongoDB return an error that you should pass a string to convert to Date in dateFromString! so i suggest to you to get yesterday Date in metabase write code something like this : 

[{
  "$project": {
      "user": 1,
      "createdAt": 1,
      "yesterday": {
          "$subtract": [ISODate(), 86400000]
      }
  }
},
{
 "$project": {
     "user": 1,
     "yesterday": 1,
     "createdAt": 1,
     "dateComp": { "$cmp": ["$yesterday", "$createdAt"] }
 }
},
{
"$match": { 
     "dateComp": -1 
}}]

Upvotes: 3

TacoPurplePlox
TacoPurplePlox

Reputation: 11

In Metabase, you can actually use dates relative to today, such as yesterday, by using Date(). This is a workaround currently implemented in the mongo driver for metabase. This workaround internally handles the date as a String, so we have to parse it before trying to use yesterday as a Date.

[
{
    "$project": {
        "_id": 1,
        "todayHandledAsString": Date(),
        "todayHandledAsDate": {"$dateFromString": {
            "dateString": Date()
        }}
    }
},
{
    "$project": {
        "_id": 1,
        "todayHandledAsString": 1,
        "todayHandledAsDate": 1,
        "yesterday": {
            "$subtract": ["$todayHandledAsDate", 86400000]
        }
    }
}
]

Unfortunately, Metabase also disallows operations or comments, so we have to use a hardcoded value of 86400000 instead of 24 * 60 * 60 * 1000, which is a single day in millis.

Upvotes: 1

Related Questions