Question 115650 in Mongodb Question posted in
The official documentation can be found here.

Converting Gregorian to Jalali date in MongoDB

How can I convert a Gregorian date to Jalali date in MongoDB?

I tried adding the javascript function mentioned in this answer to my MongoDB query in Metabase but it doesn’t support "$function" so I need a direct MongoDB query instead.

Tags:

2

Answers


  1. 0

    Unfortunately, MongoDB does not have built-in support for converting between calendar systems like Jalali and Gregorian. One way to achieve this is to write a server-side script in a language like JavaScript that performs the conversion, and then call that script using the $where operator in your MongoDB query.

    Here is an example of how you could convert a date field named "date_field" from Gregorian to Jalali using JavaScript and the $where operator:

    // JavaScript function for converting Gregorian to Jalali
function gregorian_to_jalali(gy,gm,gd){
    g_d_m=[0,31,59,90,120,151,181,212,243,273,304,334];
    if(gy>1600){
        jy=979;
        gy-=1600;
    }else{
        jy=0;
        gy-=621;
    }
    gy2=(gm>2)?(gy+1):gy;
    days=(365*gy) +(parseInt((gy2+3)/4)) -(parseInt((gy2+99)/100)) +(parseInt((gy2+399)/400)) -80 +gd +g_d_m[gm-1];
    jy+=33*(parseInt(days/12053)); 
    days%=12053;
    jy+=4*(parseInt(days/1461));
    days%=1461;
    if(days > 365){
        jy+=parseInt((days-1)/365);
        days=(days-1)%365;
    }
    jm=(days < 186)?1+parseInt(days/31):7+parseInt((days-186)/30);
    jd=1+((days < 186)?(days%31):((days-186)%30));
    return [jy,jm,jd];
}

// MongoDB query
db.collection.find(
    { $where: "gregorian_to_jalali(this.date_field.getFullYear(), this.date_field.getMonth()+1, this.date_field.getDate())[0]" }
)

    Note that this is a simple example and it may not suit all your use cases, but it is a good starting point for your implementation.
    Also note that the above script will return only the year of Jalali date, if you want to have more details of Jalali date like day and month, you need to modify the script and also the query respectively.
    I hope this helps!

    Login or Signup to reply.
  2. 0

    I had the same issue and ended up converting the code to a MongoDB query.
    Here’s an example that converts the date "2022/03/02" to "1400/12/11".

    [
 {
   "$addFields": {
     "gy": 2022,
     "gm": 3,
     "gd": 2
   }
 },
 {
   "$project": {
     "gm": true,
     "gd": true,
     "gy": {
       "$subtract": [
         "$gy",
         {
           "$cond": {
             "if": {
               "$lte": [
                 "$gy",
                 1600
               ]
             },
             "then": 621,
             "else": 1600
           }
         }
       ]
     },
     "g_d_m": [
       0,
       31,
       59,
       90,
       120,
       151,
       181,
       212,
       243,
       273,
       304,
       334
     ],
     "jy": {
       "$cond": {
         "if": {
           "$lte": [
             "$gy",
             1600
           ]
         },
         "then": 0,
         "else": 979
       }
     }
   }
 },
 {
   "$project": {
     "gy": true,
     "gm": true,
     "gd": true,
     "jy": true,
     "g_d_m": true,
     "gy2": {
       "$cond": {
         "if": {
           "$gt": [
             "$gm",
             2
           ]
         },
         "then": {
           "$add": [
             "$gy",
             1
           ]
         },
         "else": "$gy"
       }
     }
   }
 },
 {
   "$project": {
     "gy2": true,
     "gy": true,
     "gm": true,
     "gd": true,
     "jy": true,
     "g_d_m": true,
     "days": {
       "$add": [
         {
           "$multiply": [
             365,
             "$gy"
           ]
         },
         {
           "$toInt": {
             "$divide": [
               {
                 "$add": [
                   "$gy2",
                   3
                 ]
               },
               4
             ]
           }
         },
         {
           "$toInt": {
             "$divide": [
               {
                 "$add": [
                   "$gy2",
                   399
                 ]
               },
               400
             ]
           }
         },
         "$gd",
         {
           "$arrayElemAt": [
             "$g_d_m",
             {
               "$subtract": [
                 "$gm",
                 1
               ]
             }
           ]
         },
         {
           "$multiply": [
             -1,
             {
               "$add": [
                 80,
                 {
                   "$toInt": {
                     "$divide": [
                       {
                         "$add": [
                           "$gy2",
                           99
                         ]
                       },
                       100
                     ]
                   }
                 }
               ]
             }
           ]
         }
       ]
     }
   }
 },
 {
   "$project": {
     "gy2": true,
     "gy": true,
     "gm": true,
     "gd": true,
     "g_d_m": true,
     "jy": {
       "$add": [
         "$jy",
         {
           "$multiply": [
             33,
             {
               "$toInt": {
                 "$divide": [
                   "$days",
                   12053
                 ]
               }
             }
           ]
         }
       ]
     },
     "days": {
       "$mod": [
         "$days",
         12053
       ]
     }
   }
 },
 {
   "$project": {
     "gy2": true,
     "gy": true,
     "gm": true,
     "gd": true,
     "g_d_m": true,
     "jy": {
       "$add": [
         "$jy",
         {
           "$multiply": [
             4,
             {
               "$toInt": {
                 "$divide": [
                   "$days",
                   1461
                 ]
               }
             }
           ]
         }
       ]
     },
     "days": {
       "$mod": [
         "$days",
         1461
       ]
     }
   }
 },
 {
   "$project": {
     "gy2": true,
     "gy": true,
     "gm": true,
     "gd": true,
     "gdm": true,
     "jy": {
       "$add": [
         "$jy",
         {
           "$toInt": {
             "$divide": [
               {
                 "$subtract": [
                   "$days",
                   1
                 ]
               },
               365
             ]
           }
         }
       ]
     },
     "days": {
       "$cond": {
         "if": {
           "$gt": [
             "$days",
             365
           ]
         },
         "then": {
           "$mod": [
             {
               "$subtract": [
                 "$days",
                 1
               ]
             },
             365
           ]
         },
         "else": "$days"
       }
     }
   }
 },
 {
   "$project": {
     "jy": true,
     "jm": {
       "$cond": {
         "if": {
           "$lt": [
             "$days",
             186
           ]
         },
         "then": {
           "$add": [
             1,
             {
               "$toInt": {
                 "$divide": [
                   "$days",
                   31
                 ]
               }
             }
           ]
         },
         "else": {
           "$add": [
             7,
             {
               "$toInt": {
                 "$divide": [
                   {
                     "$subtract": [
                       "$days",
                       186
                     ]
                   },
                   30
                 ]
               }
             }
           ]
         }
       }
     },
     "jd": {
       "$add": [
         1,
         {
           "$cond": {
             "if": {
               "$lt": [
                 "$days",
                 186
               ]
             },
             "then": {
               "$mod": [
                 "$days",
                 31
               ]
             },
             "else": {
               "$mod": [
                 {
                   "$subtract": [
                     "$days",
                     186
                   ]
                 },
                 30
               ]
             }
           }
         }
       ]
     }
   }
 },
 {
   "$project": {
     "jy": {
       "$toInt": "$jy"
     },
     "jm": {
       "$toInt": "$jm"
     },
     "jd": {
       "$toInt": "$jd"
     }
   }
 }
]
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search