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
– Neil Lunn, Commented Apr 12, 2014 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
– user1437515, Commented Apr 14, 2014 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
– Neil Lunn, Commented Apr 14, 2014 at 10:46

Neil Lunn · Accepted Answer · 2014-04-11 13:33:29Z

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, 2014 at 13:33

Neil Lunn

151k36 gold badges356 silver badges327 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user1437515 Over a year ago

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

Neil Lunn Over a year ago

@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.

user1437515 Over a year ago

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.

Neil Lunn Over a year ago

@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.

user1437515 Over a year ago

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

Collectives™ on Stack Overflow

Date Objects in MongoDB Aggregate with Mongoose and Nodejs

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related