Uploaded image for project: 'Core Server'
  1. Core Server
  2. SERVER-93721

$dateDiff inconsistent behavior

XMLWordPrintableJSON

    • Type: Icon: Bug Bug
    • Resolution: Unresolved
    • Priority: Icon: Major - P3 Major - P3
    • None
    • Affects Version/s: 6.0.5, 7.0.12
    • Component/s: None
    • None
    • Server Triage
    • ALL
    • Hide

      Both documents have the same amount of 

      db.example.insertOne({_id: ObjectId("66c0cf6dec822937b21df7bd"), start: ISODate("2022-12-31T23:59:59"), end: ISODate("2024-01-01T00:00:00")})
db.example.insertOne({_id: ObjectId("66c0cf6dec822937b21df7be"), start: ISODate("2023-01-01T00:00:00"), end: ISODate("2024-01-01T00:00:01")})

db.example.aggregate([
    {
        $project: {
            seconds: {
                $dateDiff: {startDate: "$start", endDate: "$end", unit: "second"}
            },
            minutes: {
                $dateDiff: {startDate: "$start", endDate: "$end", unit: "minute"}
            },
            hours: {
                $dateDiff: {startDate: "$start", endDate: "$end", unit: "hour"}
            },
            days: {
                $dateDiff: {startDate: "$start", endDate: "$end", unit: "day"}
            },
            years: {
                $dateDiff: {startDate: "$start", endDate: "$end", unit: "year"}
            },
            months: {
                $dateDiff: {startDate: "$start", endDate: "$end", unit: "month"}
            }
        }
    }
])
      Show
      Both documents have the same amount of  db.example.insertOne({_id: ObjectId( "66c0cf6dec822937b21df7bd" ), start: ISODate( "2022-12-31T23:59:59" ), end: ISODate( "2024-01-01T00:00:00" )}) db.example.insertOne({_id: ObjectId( "66c0cf6dec822937b21df7be" ), start: ISODate( "2023-01-01T00:00:00" ), end: ISODate( "2024-01-01T00:00:01" )}) db.example.aggregate([ { $project: { seconds: { $dateDiff: {startDate: "$start" , endDate: "$end" , unit: "second" } }, minutes: { $dateDiff: {startDate: "$start" , endDate: "$end" , unit: "minute" } }, hours: { $dateDiff: {startDate: "$start" , endDate: "$end" , unit: "hour" } }, days: { $dateDiff: {startDate: "$start" , endDate: "$end" , unit: "day" } }, years: { $dateDiff: {startDate: "$start" , endDate: "$end" , unit: "year" } }, months: { $dateDiff: {startDate: "$start" , endDate: "$end" , unit: "month" } } } } ])

      The $dateDiff expression fails to return accurate differences for date ranges that are close together, particularly when the dates differ by small increments such as seconds. According to MongoDB's documentation, $dateDiff should return the integer difference between startDate and endDate measured in the specified units. But then it also says, "Durations are measured by counting the number of times a unit boundary is passed", which seems to contradict the first explanation and can be confusing.

      I get that you might see this as a feature rather than a bug. If that's the case, it would be really helpful to document it more clearly and include some examples. Right now, the behavior isn't obvious, and it can lead to misunderstandings.

      Here's a mongo playground link with some examples.

            Assignee:
            backlog-server-triage [HELP ONLY] Backlog - Triage Team
            Reporter:
            kucherenkovova@gmail.com Vova Kucherenko
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Created:
              Updated: