0

I am trying to get the days difference between 2 ISODates and put it in days field. I have these documents below:

{
    created_at: ISODate("2019-06-06TT00:00:00Z"),
    completed_at: "2019-06-08"
},
{
    created_at: ISODate("2019-06-06TT00:00:00Z"),
    completed_at: null
},
{
    created_at: ISODate("2019-06-06TT00:00:00Z"),
    completed_at: "2019-06-04"
}

What I have tried so far is to get the milliseconds. But I don't want that. I just want to substract the dates and get the difference in days.

db.collection.aggregate([
            {
                $addFields: {
                    completed_at: {
                        $ifNull: [
                            {
                                "$dateFromString": {
                                    "dateString": "$completed_at"
                                }
                            },
                            new ISODate()
                        ]
                    }
                }
            },
            {
                $addFields: {
                    days: {
                        $cond: [
                            {"$lte": ["$completed_at", "$created_at"]},
                            0,
                            {
                                $divide: [
                                  {
                                    $subtract: [
                                        "$completed_at",
                                        "$created_at"
                                    ]
                                  },
                                    86400000
                                ]
                            }
                        ]
                    }
                }
            },
            {
                $project: {
                    _id: 0,
                    created_at: 1,
                    completed_at: 1,
                    days: 1
                }
            }
        ])

If completed_at field is null, then the value for this should be the current date. If completed_at is lesser than created_at, then days is 0. Let's say current date is June 6, 2019, desired result should be:

{
    days: 2
},
{
    days: 0
},
{
    days: 0
}
MiniDr
  • 191
  • 1
  • 11
  • what do you mean by "I just want to substract the dates and get the difference in days." could you please explain in detail? – ArunKumar M N Jun 06 '19 at 11:24
  • I want to get the difference between 2 dates in days, disregarding time. – MiniDr Jun 06 '19 at 11:32
  • you are getting exactly in above query right? – ArunKumar M N Jun 06 '19 at 11:36
  • https://mongoplayground.net/p/t0SsblqW85I What is the issue? – Ashh Jun 06 '19 at 11:41
  • Let's say my created_at was today at 12PM and my completed_at was yesterday with no indicated time. The result should be 1 but my aggregate result above is like 0.1234 something like that. So I had to remove the time in dates to get the output that I wanted. – MiniDr Jun 06 '19 at 11:46

1 Answers1

0

I think I was able to get it. I removed the time in the date fields and the results that I wanted is showing correctly. Please let me know if I got it correctly or there are some adjustments that I need to make in order to make it faster. Thank you!

db.collection.aggregate([
  {
    $addFields: {
      created_at: {
        $dateFromParts: {
          "year": {
            $year: "$created_at"
          },
          "month": {
            $month: "$created_at"
          },
          "day": {
            $dayOfMonth: "$created_at"
          }
        }
      },
      completed_at: {
        $ifNull: [
          {
            "$dateFromString": {
              "dateString": "$completed_at"
            }
          },
          {
            $dateFromParts: {
              "year": {
                $year: new Date()
              },
              "month": {
                $month: new Date()
              },
              "day": {
                $dayOfMonth: new Date()
              }
            }
          }
        ]
      }
    }
  },
  {
    $addFields: {
      days: {
        $cond: [
          {
            "$lte": [
              "$completed_at",
              "$created_at"
            ]
          },
          0,
          {
            $divide: [
              {
                $subtract: [
                  "$completed_at",
                  "$created_at"
                ]
              },
              86400000
            ]
          }
        ]
      }
    }
  },
  {
    $project: {
      _id: 0,
      created_at: 1,
      completed_at: 1,
      days: 1
    }
  }
])
MiniDr
  • 191
  • 1
  • 11