How to properly parse Buddhist year in MongoDB?

Question

I am working with DateTime data which are stored in the Buddhist year but I need to convert it to the Gregorian year.

Actually, there is a simple solution (code below).

{
    "$dateSubtract": {
        "startDate": {
            "$dateFromString": {
                "dateString": "$receive_date",
                "format": "%d/%m/%Y %H:%M"
            }
        },
        "unit": "year",
        "amount": 543
    }
}

It works most of the time but it throws an error if receive_date is 29/2/2567 because 2567 in the Gregorian year is not a leap year but it is in the Buddhist year.

Do you have any suggestions to resolve this issue?

Answer 1

You can $split the raw dates into tokens and use $dateFromParts to reconstruct the buddhist date after the $subtract.

db.collection.aggregate([
  {
    "$set": {
      "dateTokens": {
        "$map": {
          "input": {
            "$split": [
              "$receive_date",
              "/"
            ]
          },
          "as": "token",
          "in": {
            "$toInt": "$$token"
          }
        }
      }
    }
  },
  {
    "$set": {
      "buddhist_date": {
        "$dateFromParts": {
          "year": {
            "$subtract": [
              {
                "$arrayElemAt": [
                  "$dateTokens",
                  2
                ]
              },
              543
            ]
          },
          "month": {
            "$arrayElemAt": [
              "$dateTokens",
              1
            ]
          },
          "day": {
            "$arrayElemAt": [
              "$dateTokens",
              0
            ]
          }
        }
      }
    }
  },
  {
    "$unset": "dateTokens"
  },
  {
    "$merge": {
      "into": "collection",
      "on": "_id"
    }
  }
])

Mongo Playground