1

I am trying to add and subtract few days and months from current date depending on the frequency and sequence of the notifications to be sent on these days. i.e

enter image description here

Here if the frequency is days and sequence is before means I have to notify it before given interval days from today. I am trying to construct the date based on $dateFromParts and adding or subtracting the interval days, however problem is when the month changes due to the addition or subtraction it fails to give correct date. Here is the pipeline stage I am using to calculate the expiry date -

expirydate : {
        $cond : [
            {
                $eq : [
                    '$sequence',
                    'Before'
                ]
            },
            {
                $cond : [
                    {
                        $eq : [
                            '$recurrence',
                            'days'
                        ]
                    },
                    {
                        $dateFromParts : {
                            year : {
                                $year : '$today'
                            },
                            month : {
                                $month : '$today'
                            },
                            day : {
                                $subtract : [
                                    '$interval',
                                    {
                                        $dayOfMonth : "$today"
                                    }
                                ]
                            },
                            hour : {
                                $hour : "$today"
                            },
                            minute : {
                                $minute : "$today"
                            },
                            second : {
                                $second : "$today"
                            }
                        }
                    },
                    {
                        $dateFromParts : {
                            year : {
                                $year : '$today'
                            },
                            month : {
                                $subtract : [
                                    '$interval',
                                    {
                                        $month : '$today'
                                    }
                                ]
                            },
                            day : {
                                $dayOfMonth : "$today"
                            },
                            hour : {
                                $hour : "$today"
                            },
                            minute : {
                                $minute : "$today"
                            },
                            second : {
                                $second : "$today"
                            }
                        }
                    }
                ]
            },
            {
                $cond : [
                    {
                        $eq : [
                            '$recurrence',
                            'days'
                        ]
                    },
                    {
                        $dateFromParts : {
                            year : {
                                $year : '$today'
                            },
                            month : {
                                $month : '$today'
                            },
                            day : {
                                $add : [
                                    '$interval',
                                    {
                                        $dayOfMonth : "$today"
                                    }
                                ]
                            },
                            hour : {
                                $hour : "$today"
                            },
                            minute : {
                                $minute : "$today"
                            },
                            second : {
                                $second : "$today"
                            }
                        }
                    },
                    {
                        $dateFromParts : {
                            year : {
                                $year : '$today'
                            },
                            month : {
                                $add : [
                                    '$interval',
                                    {
                                        $month : '$today'
                                    }
                                ]
                            },
                            day : {
                                $dayOfMonth : "$today"
                            },
                            hour : {
                                $hour : "$today"
                            },
                            minute : {
                                $minute : "$today"
                            },
                            second : {
                                $second : "$today"
                            }
                        }
                    }
                ]
            }
        ]
    }

and with that I am getting below result, which isn't correct. I have mongodb 4.4 Database hence I can't use $dateAdd or $dateSubtract. Any suggestions or pointers what should be improved or corrected.

enter image description here

Jeet
  • 5,569
  • 8
  • 43
  • 75
  • Please don't paste screenshots, use formatted text. See https://meta.stackoverflow.com/q/285551 – Wernfried Domscheit Apr 03 '22 at 20:34
  • 1
    For days you can simply use `{$add: ["$today, {$multiply: ["$interval", 1000*60*60*24]}]}` – Wernfried Domscheit Apr 03 '22 at 20:37
  • @WernfriedDomscheit thanks for the response, yeah days could work that way but I am still not sure about how to handle the months. :( – Jeet Apr 03 '22 at 20:38
  • You can add Months and Days directly, see [Value is Greater than the Range](https://www.mongodb.com/docs/manual/reference/operator/aggregation/dateFromParts/#value-is-greater-than-the-range). `{ $dateFromParts: { 'year' : 2017, 'month' : 14, 'day': 1, 'hour' : 12 } }` gives `2018-02-01 12:00:00` – Wernfried Domscheit Apr 03 '22 at 20:47
  • yeah so far seems like that is the only way. I am worried about backward compatibility of mongo 5 to 4.4, need some analysis else I would upgrade. – Jeet Apr 03 '22 at 20:48
  • 1
    You have to consider cases like "add 1 Month to 2022-01-31". You need to add exceptions for "last day of the Months". Anyway, if you implement it manually in MongoDB 4.4 then it will also work in 5.0 – Wernfried Domscheit Apr 03 '22 at 20:55

0 Answers0