Mongo query: compare multiple of the same field (timestamps) to only show documents that have a few second difference

259 Views Asked by At

I am spinning my wheels on this. I am needing to find all documents within a collection that have a timestamp ("createdTs") that have a 3 second or less difference (to be clear: month/day/time/year all the same, save those few seconds). An example of createdTs field (it's type Date): 2021-04-26T20:39:01.851Z

db.getCollection("CollectionName").aggregate([

    { $match: { memberId: ObjectId("1234") } },
     {
       $project:
         {
           year: { $year: "$createdTs" },
           month: { $month: "$createdTs" },
           day: { $dayOfMonth: "$createdTs" },
           hour: { $hour: "$createdTs" },
           minutes: { $minute: "$createdTs" },
           seconds: { $second: "$createdTs" },
           milliseconds: { $millisecond: "$createdTs" },
           dayOfYear: { $dayOfYear: "$createdTs" },
           dayOfWeek: { $dayOfWeek: "$createdTs" },
           week: { $week: "$createdTs" }
         }
     }
])

I've tried a lot of different variances. Where I'm struggling is how to compare these findings to one another. I'd also prefer to just search the entire collection and not match on the "memberId" field, just collect any documents that have less than a 3 second createdTs difference, and group/display those.

Is this possible? Newer to Mongo, and spun my wheels on this for two days now. Any advice would be greatly appreciated, thank you!

I saw this on another post, but not sure how to utilize it since I'm wanting to compare the same field:

db.collection.aggregate([
  { "$project": {
    "difference": {
      "$divide": [
        { "$subtract": ["$logoutTime", "$loginTime"] },
        60 * 1000 * 60
      ]
    }
  }},
  { "$group": {
    "_id": "$studentID",
    "totalDifference": { "$sum": "$difference" }
  }},
  { "$match": { "totalDifference": { "$gte": 20 }}}
])

Also am trying...

db.getCollection("CollectionName").aggregate([
    { $match: { memberId: ObjectId("1234") } },
    {
        $project:
            {
                year: { $year: "$createdTs" },
                month: { $month: "$createdTs" },
                total:
                    { $add: ["$year", "$month"] }
            }
    }
])

But this returns a total of null. Not sure if it's because $year and $month are difference? The types are both int32, so I thought that'd work. Was wondering if there's a way to compare if all the fields are 0, then if seconds is not/difference is $gte 3 when using $group, could go from there.

0

There are 0 best solutions below