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.
CodePudding user response：
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"
}
}
}
]
CodePudding user response：
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)65;
}
jm=(days < 186)?1 parseInt(days/31):7 parseInt((days-186)/30);
jd=1 ((days < 186)?(days1):((days-186)0));
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!