'How to convert string time format to date or time in mongodb

I am facing issue while converting string time format i.e., 0h 0m 53s+695ms to date or time I have a duration field with this format and is of type string , I have to add the duration of all the records in the collection matching on certain condition.

But I am getting error like : Failed to parse number '0h 0m 53s+695ms' in $convert with no onError value: Bad digit "h" while parsing 0h 0m 53s+695ms" (Note: if I specify onError than the result only get populates with error conditions) and many more of such kind.

Any clue on how to convert this specific type to data?

mongodbmongodb-queryaggregation-framework


Solution 1:[1]

This aggregation pipeline will convert the string into number of seconds. These you can sum up:

db.collection.aggregate([
   { $set: { t: { $first: { $regexFindAll: { input: "$t", regex: /^(\d+)h (\d+)m (\d+)s\+(\d+)ms$/ } } } } },
   {
      $set: {
         t: {
            $sum: [
               { $multiply: [{ $toInt: { $arrayElemAt: ["$t.captures", 0] } }, 60 * 60] },
               { $multiply: [{ $toInt: { $arrayElemAt: ["$t.captures", 1] } }, 60] },
               { $toInt: { $arrayElemAt: ["$t.captures", 2] } },
               { $divide: [{ $toInt: { $arrayElemAt: ["$t.captures", 2] } }, 1000] }
            ]
         }
      }
   }
])

The output you can format like this:

db.collection.aggregate([
   { $set: { t: { $first: { $regexFindAll: { input: "$t", regex: /^(\d+)h (\d+)m (\d+)s\+(\d+)ms$/ } } } } },
   {
      $set: {
         t: {
            $sum: [
               { $multiply: [{ $toInt: { $arrayElemAt: ["$t.captures", 0] } }, 60 * 60] },
               { $multiply: [{ $toInt: { $arrayElemAt: ["$t.captures", 1] } }, 60] },
               { $toInt: { $arrayElemAt: ["$t.captures", 2] } },
               { $divide: [{ $toInt: { $arrayElemAt: ["$t.captures", 2] } }, 1000] }
            ]
         }
      }
   },
   {
      $set: {
         t: {
            $dateToString: {
               date: { $toDate: { $multiply: ["$t", 1000] } },
               format: "%H:%M:%S.%L"
            }
         }
      }
   }
])

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Wernfried Domscheit

Related Questions