Need to compare 2 different date fields in mongodb

Question

I have two date fields in my Mongo Database and I would like to compare their difference (i.e. their "Age") with a value a value in years.

For example: I would like to find the Pharmacies that are older than 15 years.

(CurrentDate - EstablishedDate) > 15 (keep in mind the CurrentYear as it might not be of today's date) What is the way to do this?

Sample array of documents below:

[
  {
    "Pharmacy": "a",
    "EstablishedDate": ISODate("2006-10-12"),
    "CurrentDate": ISODate("2018-07-17"),
    "Medicine": [
      {
        "MedName": "MedA",
        "Quantity": 55,
        "Type": "Admission"
      },
      {
        "MedName": "MedB",
        "Quantity": 34,
        "Type": "Admission"
      },
      {
        "MedName": "MedD",
        "Quantity": 25,
        "Type": "Discharge",

      }
    ]
  },
  {
    "Pharmacy": "b",
    "EstablishedDate": ISODate("2015-02-02"),
    "CurrentDate": ISODate("2018-07-17"),
    "Medicine": [
      {
        "MedName": "MedB",
        "Quantity": 60,
        "Type": "Critical"
      },
      {
        "MedName": "MedC",
        "Quantity": 34,
        "Type": "mild"
      }
    ]
  }
]

You can use aggregation pipeline and its `$subtract` operator: https://docs.mongodb.com/manual/reference/operator/aggregation/subtract/ — Sergio Tulentsev, Jul 17 '18 at 10:25
https://docs.mongodb.com/manual/reference/operator/query/expr/ to use aggregation expressions in a find query. As a side note, having CurrentDate as a property of a document is quite unusual. Can't imagine a usecase where time flows differently for different documents. — Alex Blex, Jul 17 '18 at 10:29
@AlexBlex yeah the CurrentDate is misleading. Consider it a Date field that is definitely older than that of the pharmacy. I'll change it if it makes things clearer. — Acex, Jul 17 '18 at 10:34
So do you mean we need to find the pharmacies whose "EstablishedDate" is greater than "CurrentDate"? — Subhashree Pradhan, Jul 17 '18 at 10:39
No, I need the (CurrentDate - EstablishedDate) > 15. I'll update my question with this. — Acex, Jul 17 '18 at 10:43
Your second document contains `CurrentDate` but first one does not? — Ashh, Jul 17 '18 at 10:50
Oops, my bad, this is just a quick sample I wrote down, fixed it. — Acex, Jul 17 '18 at 10:52
Try this https://stackoverflow.com/questions/50862096/convert-string-to-date-and-get-difference/50864332#50864332 — Ashh, Jul 17 '18 at 10:52
@AnthonyWinzlet In their case they are using only one date and comparing it with the Date() operation which returns the current Day's date, but I want it to be compared with the other date field present. — Acex, Jul 17 '18 at 10:57

score 2 · Answer 1 · answered Jul 17 '18 at 12:22

2

You can use date time operator $year to extract the year.

Something like

db.colname.find({"$expr":{
  "$gt":[
    {"$subtract":[
      {"$year":"$CurrentDate"},
      {"$year":"$EstablishedDate"}
    ]},
    15
  ]
}})

answered Jul 17 '18 at 12:22

s7vr

73,656
11
106
127

Ashh · Accepted Answer · 2018-07-17T11:04:53.950

1

You can try below aggregation

 db.collection.aggregate([
  {
    "$redact": {
      "$cond": [
        {
          "$lt": [
            {
              "$divide": [
                {
                  "$subtract": [
                    "$CurrentDate",
                    "$EstablishedDate"
                  ]
                },
                1000 * 60 * 60 * 24
              ]
            },
            365 * 15
          ]
        },
        "$$KEEP",
        "$$PRUNE"
      ]
    }
  }
])

edited Jul 17 '18 at 11:04

answered Jul 17 '18 at 10:47

Ashh

44,693
14
105
132

1

yes this pretty much answers my question, I am using pipeline though, but I extracted the bits that were confusing me from here. Thank you! – Acex Jul 17 '18 at 11:25

score 0 · Answer 3 · answered Jul 17 '18 at 11:24

Please check this aggregate query :

db.test.aggregate([
    {
    "$redact": {
        "$cond": [{
            "$gt": [{ 
                "$divide": [{
                    "$subtract": ["$CurrentDate", "$EstablishedDate"] },
                    1000 * 60 * 60 * 24 * 365
                ]},
            15
            ]},
            "$$KEEP",
            "$$PRUNE"
            ]
        }
    }
])

Here we are subtracting the CurrentDate and EstablishedDate and finding the difference between them in years. If it is greater than 15, only then we'll display them.

Try this & let me know if it worked for you.

Need to compare 2 different date fields in mongodb

3 Answers3