$dateAdd incorrectly adds months/quarters for certain dates

XMLWordPrintableJSON

    • Type: Bug
    • Resolution: Fixed
    • Priority: Major - P3
    • 4.9.0
    • Affects Version/s: 4.9.0-alpha1, 5.0 Required
    • Component/s: Querying
    • None
    • Fully Compatible
    • ALL
    • Hide

      Reproduction steps when the unit is month.

      > db.c.insert({});

// Correct.
> db.c.aggregate([{
    $project: {
        datePlus1Month: {
            $dateToString: {
                date: {
                    $dateAdd: {
                        startDate: {
                            $dateFromString:
                                {dateString: "2021-02-01T00:00:00.000", timezone: "UTC"}
                        },
                        unit: "month",
                        amount: 1,
                        timezone: "UTC"
                    }
                },
                timezone: "UTC"
            }
        }
    }
}])
{ "_id": ObjectId("603cbeb2ffe24760edc177bb"), "datePlus1Month": "2021-03-01T00:00:00.000Z" }

// Incorrect.
> db.c.aggregate([{
    $project: {
        datePlus1Month: {
            $dateToString: {
                date: {
                    $dateAdd: {
                        startDate: {
                            $dateFromString:
                                {dateString: "2021-02-01T00:00:00.000", timezone: "Europe/Sofia"}
                        },
                        unit: "month",
                        amount: 1,
                        timezone: "Europe/Sofia"
                    }
                },
                timezone: "Europe/Sofia"
            }
        }
    }
}])
{ "_id": ObjectId("603cbeb2ffe24760edc177bb"), "datePlus1Month": "2021-03-04T00:00:00.000Z" }

// Correct.
> db.c.aggregate([{
    $project: {
        datePlus1Month: {
            $dateToString: {
                date: {
                    $dateAdd: {
                        startDate: {
                            $dateFromString: {
                                dateString: "2021-02-01T00:00:00.000",
                                timezone: "America/New_York"
                            }
                        },
                        unit: "month",
                        amount: 1,
                        timezone: "America/New_York"
                    }
                },
                timezone: "America/New_York"
            }
        }
    }
}])
{ "_id": ObjectId("603cbeb2ffe24760edc177bb"), "datePlus1Month": "2021-03-01T00:00:00.000Z" }

// Correct.
> db.c.aggregate([{
    $project: {
        datePlus1Month: {
            $dateToString: {
                date: {
                    $dateAdd: {
                        startDate: {
                            $dateFromString:
                                {dateString: "2021-04-30T20:00:00.000", timezone: "UTC"}
                        },
                        unit: "month",
                        amount: 1,
                        timezone: "UTC"
                    }
                },
                timezone: "UTC"
            }
        }
    }
}])
{ "_id": ObjectId("603cbeb2ffe24760edc177bb"), "datePlus1Month": "2021-05-30T20:00:00.000Z" }

// Incorrect.
db.c.aggregate([{
    $project: {
        datePlus1Month: {
            $dateToString: {
                date: {
                    $dateAdd: {
                        startDate: {
                            $dateFromString: {
                                dateString: "2021-04-30T20:00:00.000",
                                timezone: "America/New_York"
                            }
                        },
                        unit: "month",
                        amount: 1,
                        timezone: "America/New_York"
                    }
                },
                timezone: "America/New_York"
            }
        }
    }
}])
{ "_id": ObjectId("603cbeb2ffe24760edc177bb"), "datePlus1Month": "2021-05-31T20:00:00.000Z" }
      Show
      Reproduction steps when the unit is month. > db.c.insert({}); // Correct. > db.c.aggregate([{ $project: { datePlus1Month: { $dateToString: { date: { $dateAdd: { startDate: { $dateFromString: {dateString: "2021-02-01T00:00:00.000" , timezone: "UTC" } }, unit: "month" , amount: 1, timezone: "UTC" } }, timezone: "UTC" } } } }]) { "_id" : ObjectId( "603cbeb2ffe24760edc177bb" ), "datePlus1Month" : "2021-03-01T00:00:00.000Z" } // Incorrect. > db.c.aggregate([{ $project: { datePlus1Month: { $dateToString: { date: { $dateAdd: { startDate: { $dateFromString: {dateString: "2021-02-01T00:00:00.000" , timezone: "Europe/Sofia" } }, unit: "month" , amount: 1, timezone: "Europe/Sofia" } }, timezone: "Europe/Sofia" } } } }]) { "_id" : ObjectId( "603cbeb2ffe24760edc177bb" ), "datePlus1Month" : "2021-03-04T00:00:00.000Z" } // Correct. > db.c.aggregate([{ $project: { datePlus1Month: { $dateToString: { date: { $dateAdd: { startDate: { $dateFromString: { dateString: "2021-02-01T00:00:00.000" , timezone: "America/New_York" } }, unit: "month" , amount: 1, timezone: "America/New_York" } }, timezone: "America/New_York" } } } }]) { "_id" : ObjectId( "603cbeb2ffe24760edc177bb" ), "datePlus1Month" : "2021-03-01T00:00:00.000Z" } // Correct. > db.c.aggregate([{ $project: { datePlus1Month: { $dateToString: { date: { $dateAdd: { startDate: { $dateFromString: {dateString: "2021-04-30T20:00:00.000" , timezone: "UTC" } }, unit: "month" , amount: 1, timezone: "UTC" } }, timezone: "UTC" } } } }]) { "_id" : ObjectId( "603cbeb2ffe24760edc177bb" ), "datePlus1Month" : "2021-05-30T20:00:00.000Z" } // Incorrect. db.c.aggregate([{ $project: { datePlus1Month: { $dateToString: { date: { $dateAdd: { startDate: { $dateFromString: { dateString: "2021-04-30T20:00:00.000" , timezone: "America/New_York" } }, unit: "month" , amount: 1, timezone: "America/New_York" } }, timezone: "America/New_York" } } } }]) { "_id" : ObjectId( "603cbeb2ffe24760edc177bb" ), "datePlus1Month" : "2021-05-31T20:00:00.000Z" }
    • Query Execution 2021-03-08, Query Execution 2021-03-22
    • None
    • None
    • None
    • None
    • None
    • None
    • None

      Expression $dateAdd incorrectly adds months/quarters for certain inputs - non-UTC locales and dates that are at the start/end of a month. 

            Assignee:
            Milena Ivanova
            Reporter:
            Mindaugas Malinauskas
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Created:
              Updated:
              Resolved: