0

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.

Neil Lunn
  • 148,042
  • 36
  • 346
  • 317
mariotux
  • 36
  • 1
  • 3

2 Answers2

1

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]}]}}}}])
Alex
  • 21,273
  • 10
  • 61
  • 73
0

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.

Srikanta
  • 1,145
  • 2
  • 12
  • 22