How to subtract and add days and months in a mongodb 4.4 aggregation

848 views Asked by At

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

0

There are 0 answers