如何在MongoDB中实现公历转Jalali日期？Metabase不支持$function

由于Metabase不支持MongoDB的$function操作符，我们可以通过原生聚合管道操作，将公历转Jalali日期的算法转换成MongoDB内置操作符组合，直接实现转换需求。

完整聚合查询示例 #

db.collection.aggregate([
  // 提取公历日期的年、月、日
  {
    $addFields: {
      gregorianYear: { $year: "$yourDateField" },
      gregorianMonth: { $month: "$yourDateField" },
      gregorianDay: { $dayOfMonth: "$yourDateField" }
    }
  },
  // 核心转换计算逻辑
  {
    $addFields: {
      jalaliCalculations: {
        $let: {
          vars: {
            gYear: "$gregorianYear",
            gMonth: "$gregorianMonth",
            gDay: "$gregorianDay"
          },
          in: {
            $let: {
              vars: {
                gMonthAdjusted: { $cond: [{ $gte: ["$$gMonth", 3] }, "$$gMonth", { $add: ["$$gMonth", 12] }] },
                gYearAdjusted: { $cond: [{ $gte: ["$$gMonth", 3] }, "$$gYear", { $subtract: ["$$gYear", 1] }] },
                dayCount: { $add: [{ $multiply: [{ $subtract: ["$$gMonthAdjusted", 3] }, 30.6001] }, "$$gDay"] },
                julianDay: { $add: [
                  { $multiply: ["$$gYearAdjusted", 365] },
                  { $floor: { $divide: ["$$gYearAdjusted", 4] } },
                  { $floor: { $divide: ["$$gYearAdjusted", 400] } },
                  { $subtract: 0, { $floor: { $divide: ["$$gYearAdjusted", 100] } } },
                  1721119,
                  { $floor: "$$dayCount" }
                ] },
                jalaliEpoch: 1948320.5
              },
              in: {
                $let: {
                  vars: {
                    jalaliDay: { $subtract: ["$$julianDay", "$$jalaliEpoch"] },
                    jalaliYear: { $floor: { $divide: ["$$jalaliDay", 365.2422] } },
                    jalaliYearStart: { $add: ["$$jalaliEpoch", { $multiply: ["$$jalaliYear", 365.2422] }] },
                    jalaliDayOfYear: { $floor: { $subtract: ["$$jalaliDay", "$$jalaliYearStart"] } }
                  },
                  in: {
                    jalaliYear: { $add: ["$$jalaliYear", 1] },
                    jalaliMonth: { $add: [{ $floor: { $divide: ["$$jalaliDayOfYear", 30.6001] } }, 1] },
                    jalaliDay: { $add: [{ $floor: { $subtract: ["$$jalaliDayOfYear", { $multiply: [{ $floor: { $divide: ["$$jalaliDayOfYear", 30.6001] } }, 30.6001] }] } }, 1] }
                  }
                }
              }
            }
          }
        }
      }
    }
  },
  // 格式化生成标准Jalali日期字符串
  {
    $addFields: {
      jalaliDate: {
        $concat: [
          { $toString: "$jalaliCalculations.jalaliYear" },
          "-",
          { $toString: { $cond: [{ $lt: ["$jalaliCalculations.jalaliMonth", 10] }, { $concat: ["0", { $toString: "$jalaliCalculations.jalaliMonth" }] }, "$jalaliCalculations.jalaliMonth"] } },
          "-",
          { $toString: { $cond: [{ $lt: ["$jalaliCalculations.jalaliDay", 10] }, { $concat: ["0", { $toString: "$jalaliCalculations.jalaliDay" }] }, "$jalaliCalculations.jalaliDay"] } }
        ]
      }
    }
  },
  // 可选：移除中间计算字段，精简结果
  {
    $project: {
      gregorianYear: 0,
      gregorianMonth: 0,
      gregorianDay: 0,
      jalaliCalculations: 0
    }
  }
])

使用说明 #

1. 将查询中的$yourDateField替换为你集合中存储公历日期的字段名
2. 最终结果会新增jalaliDate字段，格式为YYYY-MM-DD的Jalali日期字符串
3. 若需要其他格式，可以修改$concat部分的拼接逻辑

内容的提问来源于stack exchange，提问作者Mo hashemi