Date Objects in MongoDB Aggregate with Mongoose and Nodejs

Question

I am trying to operate on Date objects within an aggregation from nodejs with mongoose. My DB document has the following form:

{"__v" : 0,
"dob" : ISODate("1991-04-10T11:41:02.361Z"),
"gender" : "MALE",
...}

I am trying calculate the current age as follows, which works fine in robomongo and from the shell

db.p.aggregate([
{$project: { "age": {$divide: [{ $subtract: [ new ISODate(),  "$dob"  ] },31558464000]}}}
])

Output:

"result" : [ 
    {
        "age" : 23.00286577030492,
        "gender" : "MALE"
    }, 
    ...
]

But I cannot get it to work from within mongoose. If I use the same query, ISODate is not defined as stated here: ISODate is not defined

So as proposed I tried different variations of Date()

{$project: { "age": {$divide: [{ $subtract: [ new Date(),  "$dob"  ] }, 31558464000] }}}
RESULT: [MongoError: exception: cant $subtract a String from a Date]

{$project: {
   "age": {$divide: [{ $subtract: [ new Date(),  new Date("$dob")  ] }, 31558464000] },
   "dob2": { $subtract: [ new Date("$dob"), 1  ]  }, "dob": 1}}
RESULT: [ { _id: '10000000000000000000000b',
dob: '1991-04-10T11:41:02.361Z',
age: 44.27401739168928,                          <-- wrong age
dob2: Thu Jan 01 1970 00:59:59 GMT+0100 (CET) }, <-- wrong date
... ]

 {$project: {
   "age": {$divide: [{ $subtract: [ new Date(),  Date("$dob")  ] }, 31558464000] },
   "dob": 1}}
 RESULT: [MongoError: exception: cant $subtract a String from a Date]

If I try to convert the String to a date within the aggregation, it fails to convert correctly but outside of aggregate it works fine:

var dateISOString = "1991-04-10T11:41:02.361Z";
var dateTimeDate = new Date(startTimeISOString);
RESULT: Wed Apr 10 1991 13:41:02 GMT+0200 (CEST) 23.00290412198135  <-- correct Date

So I cannot use the direct reference $dob because it is treated as a String. I cannot use ISODate conversion because it is not defined and I cannot use new Date(), because the conversion is wrong within the aggregation framework. I have googled extensively, but could not find a solution to the problem. How can I either convert my ISODate string to the correct date or directly get a date in the first place without, leaving the aggregation. Help is much appreciated.

MongoDB: "version" : "2.4.9",
"sysInfo" : "Linux SMP Fri Nov 20 17:48:28 EST 2009 x86_64 BOOST_LIB_VERSION=1_49",

nodejs: v0.10.21

Edit after Neil's reply:

     function test( user, callback ) {
        var mongoose = require( 'mongoose' );

        mongoose.connect( 'mongodb://localhost:8080/11', function( ) {  } );


            var
                collection;
            if( err ) {
                callback( err );
            } else {
                collection = mongoose.connections[0].collection( 'aggregate' );

                        collection.aggregate([
                            { "$project": {
                                "_id": 0,
                                "this": { "$divide": [
                                    { "$subtract": [ new Date(), "$dob" ]},
                                    31558464000
                                ]}
                            }}
                        ], function( err, res ) {
                            console.warn( 'collection.doneCb.1', res, JSON.stringify( res ) );
                            callback( err, res );
                        } );

                        /*collection.aggregate( query, function( err, res ) {
                         console.warn( 'collection.doneCb.1', res, JSON.stringify( res ) );
                         callback( err, res );
                         } );*/
                    } );

                } );

            }

};

This produces:

{ [MongoError: exception: cant $subtract a String from a Date]
name: 'MongoError',
errmsg: 'exception: cant $subtract a String from a Date',
code: 16613,
ok: 0 }

Thanks!

Did the answer not explain your problem properly? I see no response. — Neil Lunn, Apr 12 '14 at 12:24
Thank you Neil. It is a work related question so over the weekend I did not check, sorry for the delay. I will reply in a couple of hours after my appointment, but at first glance I see no mongoose connection in your reply. There are no problems when I query from shell. From mongoose the reference to a date produces a string (first non working query) — user1437515, Apr 14 '14 at 10:33
I actually wrote out the response as fairly generic method as it does have a possibility of applying outside of Mongoose itself, and is basically just JavaScript. So actually since there is no `ISODate` function available to JavaScript with "node" or "Mongoose" then it basically solves. For Mongoose in particular, then just replace the `db.birthdays` part with the name of your actual model and the appropriate callback function after the conditions. — Neil Lunn, Apr 14 '14 at 10:46

score 1 · Accepted Answer · answered Apr 11 '14 at 13:33

1

So just to clarify the output. ISODate is an internal function to MongoDB shell, or is otherwise provided in a driver function. But for JavaScript just use the Date() object constructor:

Given the data:

{ "date" : ISODate("1971-09-22T00:00:00Z") }

The following query:

db.birthdays.aggregate([
    { "$project": { 
        "_id": 0,
        "this": { "$divide": [
            { "$subtract": [ new Date(), "$date" ]}, 
            31558464000 
        ]} 
    }}
])

Produces:

{ "this" : 42.552055734461604 }

Even though I'm sorry to say it ;-)

answered Apr 11 '14 at 13:33

Neil Lunn

148,042
36
346
317

Thank you again, Neil. Horror meeting.. Anyway the thing is it does not work. As I understand you, using Mongoose should not influence the result and the call is a simple Mongo query wrapped in classic JS. And that is how I used it. But it fails. I extend my initial question with a minimal example and your query including the resulting error. Best Lars – user1437515 Apr 14 '14 at 13:53
@user1437515 So what is so obscure about the error message? Your date is not a date "it's actually a string" as the error tells you. So you need to fix your dates. Honestly the most common mistake with MongoDB. The query works perfectly when your data is correct. – Neil Lunn Apr 14 '14 at 14:09
The message is clear but my data is not a string. The document in MongoDB has ISODates: "_id" : ObjectId("111"), "dob" : ISODate("1991-04-10T11:41:02.361Z"), If I work directly on MongoDB I have got dates. Just from mongoose/nodejs it gets resolved as string. – user1437515 Apr 14 '14 at 14:17
@user1437515 There is no reason for the above example to not work if that is the case. But you have actually posted the error message that refutes your claim quite clearly. If not **all** then at least one of the documents in your collection caught by the query clearly has a string in the date. Otherwise the error would not be emitted. – Neil Lunn Apr 14 '14 at 14:22
Thank you so much for your patience! Your comments made me look in a different direction. The data in the DB was dates. But my colleague, who I took over from because he is in holdays, was inserting new test data for every run and did not operate on the db data... Sorry for your troubles and my slow understanding – user1437515 Apr 14 '14 at 14:41

Date Objects in MongoDB Aggregate with Mongoose and Nodejs

1 Answers1