[SERVER-54845] $dateAdd incorrectly adds months/quarters for certain dates Created: 01/Mar/21  Updated: 29/Oct/23  Resolved: 19/Mar/21

Status: Closed
Project: Core Server
Component/s: Querying
Affects Version/s: 4.9.0-alpha1, 5.0 Required
Fix Version/s: 4.9.0

Type: Bug Priority: Major - P3
Reporter: Mindaugas Malinauskas Assignee: Milena Ivanova
Resolution: Fixed Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Depends
is depended on by SERVER-54390 Implement $dateTrunc in DocumentSourc... Closed
Backwards Compatibility: Fully Compatible
Operating System: ALL
Steps To Reproduce:

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" }

Sprint: Query Execution 2021-03-08, Query Execution 2021-03-22
Participants:

 Description   

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



 Comments   
Comment by Githook User [ 10/Mar/21 ]

Author:

{'name': 'Milena Ivanova', 'email': 'milena.ivanova@mongodb.com', 'username': 'mivanova3'}

Message: SERVER-54845 Fix $dateAdd last-day-of-month adjustment in timezone.
Branch: master
https://github.com/mongodb/mongo/commit/be1101d6c59a89df2a808de6a5c4b2d1e24d6d28

Generated at Thu Feb 08 05:34:39 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.