How to get a record within a date range in mongodb?

Question

In my mongodb collection I have a record with:

//mongodb field: "birth_date": "1983-05-06T16:26:32.613Z"

And here my find query, to get that record in the range:

var birthYear = 1983;
var birthDateStart = new Date('1.1.' + birthYear); //Sat Jan 01 1983 00:00:00 GMT+0100 (Mitteleuropäische Zeit)
var birthDateEnd = new Date('12.30.' + birthYear); //Fri Dec 30 1983 00:00:00 GMT+0100 (Mitteleuropäische Zeit)
    var cursor = db.collection('users').find({
            birth_date: {$gte: birthDateStart, $lt: birthDateEnd}
        })

I think the problem is the date format, how can I get the same Date() format as that in the database?

I used variety to get the DB schema:

+--------------------------------------------------+
| key          | types    | occurrences | percents |
| ------------ | -------- | ----------- | -------- |
| _id          | ObjectId |           1 |    100.0 |
| bio          | String   |           1 |    100.0 |
| birth_date   | String   |           1 |    100.0 |
+--------------------------------------------------+

I use the 'mongodb' package for express.js - and can't use ISODate().

ReferenceError: ISODate is not defined

It is in that format. You are looking at the "stringified" form. But your data could possibly be strings themselves. Show your actual data as viewed from the [Mongo Shell.](https://docs.mongodb.com/manual/reference/program/mongo/#bin.mongo). That will tell us for certain what your `"birth_date` data actually looks like. — Neil Lunn, Jun 19 '17 at 10:54
Also should be `new Date("yyyy-mm-dd")` to actually construct a `Date` object in the UTC time period as your MongoDB data should be stored. But again, we need to see the actual representation of the data to be sure. — Neil Lunn, Jun 19 '17 at 11:11
Possible duplicate of [Find objects between two dates MongoDB](https://stackoverflow.com/questions/2943222/find-objects-between-two-dates-mongodb) — Mustafa Mamun, Jun 19 '17 at 11:22
You need to fix the data first. But I actually asked your for the 'mongo shell' output. Simply knowing it's a string does not really help because we do not know the actual format of that string. Please just use the mongo shell as asked and include that information in your question. — Neil Lunn, Jun 19 '17 at 11:34
What exactly should I get from mongo shell? I can see all records with mongoexpress (phpMyAdmin for mongodb) and the birthdate looks like this: "birth_date": "1983-05-06T16:26:32.613Z" and is a String. — Suisse, Jun 19 '17 at 11:36
@NeilLunn What should I get with mongo shell? should I use the find() method to get one record and paste it here? I don't understand what you mean. thx — Suisse, Jun 19 '17 at 11:54
Please **do not** parse dates like `new Date('1.1.' + birthYear)`, see [*Why does Date.parse give incorrect results?*](https://stackoverflow.com/questions/2587345/why-does-date-parse-give-incorrect-results) Far better to use `new Date(birthYear, 0, 1)` for the start and `new Date(birthYear, 11, 31, 23, 59, 59, 999)` for the end. Both of which are unambiguous and compliant with every edition of ECMAScript. — RobG, Jun 19 '17 at 11:56

score 0 · Accepted Answer · answered Jun 19 '17 at 15:44

Thank you for all your hints. The problem was, that the birth_date field was saved as String.

I want to show you in this answer how to change the format of a field from String to Date in all records in your collection and then how to search by Age (in years) when you have saved only the birthdate. Maybe it helps someone else.

Change the String format to Date of the field birth_date:

mongo.connect(url, function (err, db) {
        console.log(err);
        var resultArray = [];
        var cursor = db.collection('users').find({});
        cursor.forEach(function (doc, err) {
            console.log(doc);
            db.collection('users').update({_id: doc._id}, {$set: {birth_date: new Date(doc.birth_date)}});
        }, function () {
            db.close();
            res.send("Done!");
        })
    })

Find a user who is 18years old:

var search_age = 18;
var birthYear = new Date(Date.now()).getFullYear() - search_age;
var birthDateStart = new Date(birthYear, 0, 1);
var birthDateEnd = new Date(birthYear, 11, 31, 23, 59, 59, 999);
var resultArray = [];

mongo.connect(url, function (err, db) {
    console.log(err);
    var resultArray = [];
    var cursor = db.collection('users').find({
            birth_date: {$gte: birthDateStart, $lt: birthDateEnd},
        })
        ;
    cursor.forEach(function (doc, err) {
        resultArray.push(doc);
    }, function () {
        db.close();
        res.send(resultArray);
    })
})

You can accept your own answer, that way others will know it's answered and can link it as a duplicate if other similar questions are asked. ;-) — RobG, Jun 19 '17 at 22:29

How to get a record within a date range in mongodb?

1 Answers1