MongoDB aggregation NumberLong to Date

Question

The example document of mongodb:

{
    "_id" : ObjectId("512eef329d5d0c9415000025"),
    "time" : NumberLong(1431973102)
}

I want to get the $dayOfMonth from a timestamp value saved in time attribute in aggreate query but I've this excepcion message:

uncaught exception: aggreate failed: {
   "errmsg" : "exception: can't convert from BSON type NumberLong64 to Date",
   "code" : 16006,
   "ok" : 0
}

When I run this query:

db.getCollection('collection_name').aggregate([
{ $match: { '_id': ObjectId('512eef329d5d0c9415000025') } },
{ $project: { 'time': 1, "dayOfMonth": { $dayOfMonth: '$time' } } }
])

Any idea? Thank you.

Alex · Answer 1 · 2016-01-08T14:44:24.343

Your first need to convert your Unix epoch to a ISODate using $add

db.data.aggregate({
  $project: {
    "dayOfMonth": {
      $dayOfMonth: {
        $add: [new Date("1970-01-01"), {
          $multiply: ["$time", 1000]
        }]
      }
    }
  }
})

Implementing this to your query gives:

db.data.aggregate([
{$match: { '_id': ObjectId('512eef329d5d0c9415000025') } },
{$project: {"time": 1,"dayOfMonth": {$dayOfMonth: {$add: [new Date("1970-01-01"), {$multiply: ["$time", 1000]}]}}}}])

score 0 · Answer 2 · answered Jan 08 '16 at 14:31

If you are using Java then I would suggest you to use Joda-Time library to convert the date in epoch and extract the required month.

DateTime date = new DateTime(dateInMilli); 
date.dayOfMonth();

If the date is not used anywhere in the application then you could as well save it in the ISODate format instead of Epoch.

If you use JS then the link provided by JohnnyHK has more info.

MongoDB aggregation NumberLong to Date

2 Answers2