Convert string to date and get difference

问题:

I have few data records like this in 'food' schema -mongodb

{
    "_id": "5b220199acbec1409cbf84dd",
    "Name": "Mongo",
    "ID_No": "BA1233",
    "Content": "Bottle",
    "No_packages": 5,
    "No_items": 6,
    "Qty": 30,
    "Mfg": "2018-05-27",
    "Exp": "2018-06-30",
    "__v": 0
  }

I want to get the data set where the date difference(Exp-today Date) is less than 30. I tried it using the following command. But it didn't work.

db.food.find({$lt: { $subtract: [ Date() , Exp ],"30"}}  )

回答1:

You need to use first $dateFromString (aggregation) convert date from string to date format and then $redact (aggregation) to eliminates the unmatched documents

db.collection.aggregate([
  { "$addFields": {
    "date": {
      "$dateFromString": {
        "dateString": "$EXP"
      }
    }
  }},
  { "$redact": {
    "$cond": [
      { "$lt": [
        { "$divide": [
          { "$subtract": [new Date(), "$date"] },
          1000 * 60 * 60 * 24
        ]},
        30
      ]},
      "$$KEEP",
      "$$PRUNE"
    ]
  }}
])

回答2:

If I'm not mistaken, you want documents with Exp - today's date less than 30 days. In other words, today's date + 30 days >= Exp. Converting 30 days to milliseconds

Try the following:

db.food.aggregate(
    {
        $project: {
            _id: 0,
            Name: 1,
            exp_date_calc: new Date(ISODate().getTime() + 30 * 24 * 60 * 60 * 1000).toISOString()
        },
    }, {
        $match: { exp_date_calc: { $gt: "$Exp" } }
    }
);

or may be this one:

db.food.aggregate(
    {
        $project: {
            _id: 0,
            Name: 1,
            expdate: {
                $dateFromString: {
                    dateString: '$Exp'
                }
            },
            exp_date_calc: new Date(ISODate().getTime() + 30 * 24 * 60 * 60 * 1000)
        },
    }, {
        $match: { exp_date_calc: { $gte: "$expdate" } }
    }
);

Refer: Adding/Subtracting days to ISODate in MongoDB Shell