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