MongoDB - Convert unix time to datetime using MongoDB Compass

user_15790365 0
Viewed 740

How can we convert unix timestamp in MongoDB from 1619463804.069353 to Thu, 29 April 2021 4:55 PM

I tried using the following function but it doesn't work.

 db.collectionname.find().forEach(function(doc){
 doc['timestamp']=new Date(doc['timestamp']);
 db.collectionname.save(doc);})

Also, can a similar function be used if the timestamp field is in array object?

{
    _id: 1,
    events: [
        {
            event: 1,
            timestamp: '1619463804.069353',
        },
        {
            event: 2,
            timestamp: '1649463804.069353',
        },
        { ... },
    ]
}
mongodb unix-timestamp
1 Answers

For Mongo version 4.0+ you can use $toDate to convert this into a date object, because a unix timestamp stories the number of seconds you'll have to multiply it by a 1000 as Mongo uses the number of miliseconds that passed.

db.collection.aggregate([
  {
    $addFields: {
      events: {
        $map: {
          input: "$events",
          as: "event",
          in: {
            "$mergeObjects": [
              "$$event",
              {
                timestamp: {
                  "$toDate": {
                    $multiply: [
                      {
                        "$toDecimal": "$$event.timestamp",
                        
                      },
                      1000
                    ]
                  }
                }
              }
            ]
          }
        }
      }
    }
  }
])

Mongo Playground

For a lesser Mongo version you'll have to load the documents into memory and do it in code.

If you want to update the entire collection you can use this same pipeline with the pipelined updates feature, available starting Mongo v4.2, like so:

db.collection.updateMany(
{},
[
  {
    $addFields: {
      events: {
        $map: {
          input: "$events",
          as: "event",
          in: {
            "$mergeObjects": [
              "$$event",
              {
                timestamp: {
                  "$toDate": {
                    $multiply: [
                      {
                        "$toDecimal": "$$event.timestamp",
                        
                      },
                      1000
                    ]
                  }
                }
              }
            ]
          }
        }
      }
    }
  }
])

Mongo Playground

user_107703700
Related