Question 116108 in Mongodb Question posted in
The official documentation can be found here.

Convert Millisecond to HH:MM:SS format in MongoDB

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
},
Tags:

3

Answers


  1. 0

    Converts a value to a date

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

    toDate documentation mongodb

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
  3. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search