Hi How would I be able to convert millisecond to HH:MM:SS format in mongodb? Below is my sample data.

{ 
   "Item":"Test1",
    "millisecond" : 188760000
},
{ 
   "Item":"Test2",
    "millisecond" : 23280000
},
{ 
   "Item":"Test3",
    "millisecond" : 128820000
},

Expected output will be

{ 
   "Item":"Test1",
    "hrFormat" : 62:22:00
},
{ 
   "Item":"Test2",
    "millisecond" : 06:28:00
},
{ 
   "Item":"Test3",
    "millisecond" : 35:47:00
},

CodePudding user response：

Converts a value to a date

{$toDate: 120000000000.5}. 
//result ISODate("1973-10-20T21:20:00Z")

toDate documentation mongodb

CodePudding user response：

You could use aggregate and arithmetic operators:

db.collection.aggregate([
  {
    $project: {
      "Item": 1,
      "hours": {
        $floor: {
          $divide: [
            "$millisecond",
            3600000 // millis in 1h
          ]
        }
      },
      "minutes": {
        $floor: {
          $divide: [
            {
              $mod: [
                "$millisecond",
                3600000
              ]
            },
            60000 // millis in 1m
          ]
        }
      }
    }
  },
  {
    $project: {
      "Item": 1,
      "millisecond": {
        $concat: [
          {
            $toString: "$hours"
          },
          ":",
          {
            $toString: "$minutes"
          },
          ":00"
        ]
      }
    }
  }
])

Link to playground.

CodePudding user response：

Another solution is this one:

db.collection.aggregate([
   {
      $set: {
         hours: {
            $concat: [
               {
                  $toString: {
                     $sum: [
                        -24,
                        { $multiply: [{ $dayOfYear: { $toDate: { $toLong: "$millisecond" } } }, 24] },
                        { $hour: { $toDate: { $toLong: "$millisecond" } } },
                     ]
                  }
               },
               {
                  $dateToString: {
                     date: { $toDate: { $toLong: "$millisecond" } },
                     format: ":%M:%S"
                  }
               }
            ]
         }
      }
   }
])

Be aware, it works only for times less than one year.

Mongo Playground