Mongo DB create/update new field from existing date and time fields

Question

In mongo db have a collection named item where I have 2 fields

1. ItemReceivedDate - Type: Date , ex: value/data 2020-09-21T00:00:00.000+00:00
2. ItemReceivedTime - Type: Int. This is an 24 hours format
   ex: 1. value is 345 it is 3:45 A.M.
   2. value is 1323 -> 1:23 P.M.
   3. Value is 1 -> 12:01 A.M.
   

I want to create new field where I want to club both date and time ex: itemReceivedDate - 2020-09-21T00:00:00.000+00:00 and itemReceivedTime 1323.

itemReceivedDateAndTime - 2020-09-21T13:23:00.000+00:00

I tried below option

 db.item.updateOne({},[
    {"$set":{"itemReceivedDateAndTime":{"$toDate":{"$concat":[{"$dateToString":{format:"%Y-%m-%d",date:"$itemReceivedDate"}},"T",{"$toString":"$itemReceivedTime"}]}}}])

Its working fine when I receive time with 4 digits such as 1234 or 2322 But when I get time[int] with 3 or 2 or 1 digit above query will fail.

Any suggestions.

Thanks in advance

Answer 1

I would do it like this:

db.item.updateOne({}, [
   {
      $set: {
         itemReceivedDateAndTime: {
            $let: {
               vars: {
                  time: { $concat: ['0000', { $toString: "$ItemReceivedTime" }] },
                  len: { $strLenCP: { $concat: ['0000', { $toString: "$ItemReceivedTime" }] } }
               },
               in: {
                  $dateFromParts: {
                     year: { $year: "$ItemReceivedDate" },
                     month: { $month: "$ItemReceivedDate" },
                     day: { $dayOfMonth: "$ItemReceivedDate" },
                     hour: { $toInt: { $substrCP: ["$$time", { $subtract: ["$$len", 4] }, 2] } },
                     minute: { $toInt: { $substrCP: ["$$time", { $subtract: ["$$len", 2] }, 2] } },
                     timezone: "Europe/Zurich"
                  }
               }
            }
         }
      }
   }
])