How to find difference b/w 2 dates of different formats in mongoDB

I have a collection in mongoDB, which contains 2 fields. 1. mongodate [ISO Date Format ] 2. createtime [ epoch milliseconds in string format ]

 > db.test.find()
     { "_id" : ObjectId("5eb2bbb3b278b3c384106a20"), "mongodate" : ISODate("2020-05-05T19:42:00Z"),"createtime" : "1588366164434" }
     { "_id" : ObjectId("5eb2bcabb278b3c384106a21"), "mongodate" : ISODate("2020-05-05T19:42:00Z"), "createtime" : "1588366164434" }

I need to find average, min & max of (difference of mongodate & createtime)

But since the date formats of these 2 fields are different, so i am not ablle to use $substract properly. can somebody help me in this query?

Thanks, Rahul Verma

Solution

The createtime field looks like it might be milliseconds since epoch.

You can convert the ISODate to milliseconds in an aggregation pipeline using the $toLong operator, find the difference with $subtract, and then use a $group stage with $ave, $min, and $max operators.

db.collection.aggregate([
  {$project: {
      epoch: {$toLong: "$mongodate"},
      createepoch: {$toLong: "$createtime"}
  }},
  {$project: {
      difference: {$subtract: [
          "$epoch",
          "$createepoch"
      ]}
  }},
  {$group: {
      _id: null,
      min: {$min: "$difference"},
      max: {$max: "$difference"},
      avg: {$avg: "$difference"}
  }}
])

[Playground](https://mongoplayground.net/p/wVMdGLHhFpH)