Mongoose Queries on Date Condition Have No Results, MongoDB Shell Works

Question

I have a collection called 'indexes' that contains the 'symbol', 'price', and 'timestamp' fields.

I am trying to query this collection for items with a specific 'symbol', and that have a timestamp greater than some minDate value.

When querying the data through Mongoose I am not getting back any results when I have a condition on the 'timestamp'. Yet queries run in MongoDB shell.

I created my collection with the following schema:

    var IndexSchema = new Schema({
        symbol: {
            type: String
        },
        price: {
            type: Number
        },
        timestamp: {
            type: Date,
            default: Date.now
        }
    });

In my NodeJS application I am querying the data like this:

    var Indexes = mongoose.model('Index');

    var numDays = 7;

    var minDate = new Date();
    minDate.setDate(minDate.getDate() - (numDays));

    Indexes
    .find({'symbol':indexSymbol, 'timestamp': {$gte: minDate} })    
    .sort({'timestamp': 1});
    .exec(function (err, items) {
        console.log("There were %s items returned", items.length); // items.length == 0.
    });

Whenever running this query, regardless of date used, I'm getting back 0 results.

Even if I try running it for all past entries (timestamp less than or equal to current time) I still get back 0 results.

    var now = new Date();

    Indexes
    .find({'symbol':indexSymbol, 'timestamp': {$lte: now} })    
    .sort({'timestamp': 1});
    .exec(function (err, items) {
        console.log("There were %s items returned", items.length); // items.length == 0.
    });

I know the documents exist in my collection, If I remove the 'timestamp' condition, and query just by the 'symbol', it returns (all) the data, as expected.

    Indexes
    .find({'symbol':indexSymbol}) 
    .sort({'timestamp': 1});
    .exec(function (err, items) {
        console.log("There were %s items returned", items.length); // items.length == ~40000.
    });

In my NodeJS app I've tried formatting the date value as an ISO String, Unix Timestamp, and wrapping the '$gte' in quotes.

    // ISODate Format.
    .find({'symbol':indexSymbol, 'timestamp': {$lte: minDate.toISOString()} })    

    // Unix Timestamp
    .find({'symbol':indexSymbol, 'timestamp': {$gte: minDate.getTime() } })

    // Wrapping '$gte' in quotes.
    .find({'symbol':indexSymbol, 'timestamp': {'$gte': minDate } })

Using all of these in my NodeJS application I still get back 0 documents.

However, my query executes as I'd expect from the MongoDB shell, and from the 'Mongo Management Studio'.

    // MongoDB Shell Query using minDate.toISOString() value.

    > db.indexes.find({'symbol':'.XBT', 'timestamp': { $gte: '2015-12-09T14:57:58.588Z' } });
    { "_id" : ObjectId("566841cff485eb63b3e10375"), "symbol" : ".XBT", "price" : 421.41, "timestamp" : "2015-12-09T14:58:00.000Z" }
    { "_id" : ObjectId("566841cff485eb63b3e10376"), "symbol" : ".XBT", "price" : 421.45, "timestamp" : "2015-12-09T14:59:00.000Z" }
    { "_id" : ObjectId("56684237f485eb63b3e10378"), "symbol" : ".XBT", "price" : 421.4, "timestamp" : "2015-12-09T15:00:00.000Z" }
    { "_id" : ObjectId("56684237f485eb63b3e10379"), "symbol" : ".XBT", "price" : 421.48, "timestamp" : "2015-12-09T15:01:00.000Z" }
    { "_id" : ObjectId("566842c0f485eb63b3e1037d"), "symbol" : ".XBT", "price" : 421.18, "timestamp" : "2015-12-09T15:02:00.000Z" }
    { "_id" : ObjectId("566842c0f485eb63b3e1037e"), "symbol" : ".XBT", "price" : 421.2, "timestamp" : "2015-12-09T15:03:00.000Z" }
    { "_id" : ObjectId("56684328f485eb63b3e10380"), "symbol" : ".XBT", "price" : 421.26, "timestamp" : "2015-12-09T15:04:00.000Z" }
    { "_id" : ObjectId("56684328f485eb63b3e10381"), "symbol" : ".XBT", "price" : 420.76, "timestamp" : "2015-12-09T15:05:00.000Z" }
    { "_id" : ObjectId("566843b0f485eb63b3e10384"), "symbol" : ".XBT", "price" : 420.47, "timestamp" : "2015-12-09T15:06:00.000Z" }
    { "_id" : ObjectId("566843b0f485eb63b3e10385"), "symbol" : ".XBT", "price" : 420.35, "timestamp" : "2015-12-09T15:07:00.000Z" }
    { "_id" : ObjectId("5668441af485eb63b3e10389"), "symbol" : ".XBT", "price" : 420.19, "timestamp" : "2015-12-09T15:08:00.000Z" }
    { "_id" : ObjectId("5668441af485eb63b3e1038a"), "symbol" : ".XBT", "price" : 420.09, "timestamp" : "2015-12-09T15:09:00.000Z" }
    { "_id" : ObjectId("566844a0f485eb63b3e1038d"), "symbol" : ".XBT", "price" : 420.22, "timestamp" : "2015-12-09T15:10:00.000Z" }
    { "_id" : ObjectId("566844a0f485eb63b3e1038e"), "symbol" : ".XBT", "price" : 420.25, "timestamp" : "2015-12-09T15:11:00.000Z" }
    { "_id" : ObjectId("56684507f485eb63b3e10390"), "symbol" : ".XBT", "price" : 420.27, "timestamp" : "2015-12-09T15:12:00.000Z" }
    { "_id" : ObjectId("56684507f485eb63b3e10391"), "symbol" : ".XBT", "price" : 420.21, "timestamp" : "2015-12-09T15:13:00.000Z" }
    { "_id" : ObjectId("5668458ef485eb63b3e10395"), "symbol" : ".XBT", "price" : 420.25, "timestamp" : "2015-12-09T15:14:00.000Z" }
    { "_id" : ObjectId("5668458ef485eb63b3e10396"), "symbol" : ".XBT", "price" : 420.17, "timestamp" : "2015-12-09T15:15:00.000Z" }
    { "_id" : ObjectId("566845f6f485eb63b3e10398"), "symbol" : ".XBT", "price" : 420.11, "timestamp" : "2015-12-09T15:16:00.000Z" }
    { "_id" : ObjectId("566845f6f485eb63b3e10399"), "symbol" : ".XBT", "price" : 420.16, "timestamp" : "2015-12-09T15:17:00.000Z" }

    // MongoDB Shell Query using minDate.getTime() value.

    > db.indexes.find({'symbol':'.XBT', 'timestamp': { $gte: '1449673802572' } });
    { "_id" : ObjectId("5665dc4ff485eb63b3e04bb1"), "symbol" : ".XBT", "price" : 385.56, "timestamp" : "2015-11-07T19:21:00.000Z" }
    { "_id" : ObjectId("5665dc4ff485eb63b3e04bb2"), "symbol" : ".XBT", "price" : 385.86, "timestamp" : "2015-11-07T19:22:00.000Z" }
    { "_id" : ObjectId("5665dc4ff485eb63b3e04bb3"), "symbol" : ".XBT", "price" : 386.17, "timestamp" : "2015-11-07T19:23:00.000Z" }
    { "_id" : ObjectId("5665dc4ff485eb63b3e04bb4"), "symbol" : ".XBT", "price" : 386.43, "timestamp" : "2015-11-07T19:24:00.000Z" }
    { "_id" : ObjectId("5665dc4ff485eb63b3e04bb5"), "symbol" : ".XBT", "price" : 386.51, "timestamp" : "2015-11-07T19:25:00.000Z" }
    { "_id" : ObjectId("5665dc4ff485eb63b3e04bb6"), "symbol" : ".XBT", "price" : 386.39, "timestamp" : "2015-11-07T19:26:00.000Z" }
    { "_id" : ObjectId("5665dc4ff485eb63b3e04bb7"), "symbol" : ".XBT", "price" : 386.45, "timestamp" : "2015-11-07T19:27:00.000Z" }
    { "_id" : ObjectId("5665dc4ff485eb63b3e04bb8"), "symbol" : ".XBT", "price" : 386.45, "timestamp" : "2015-11-07T19:28:00.000Z" }
    { "_id" : ObjectId("5665dc4ff485eb63b3e04bb9"), "symbol" : ".XBT", "price" : 386.85, "timestamp" : "2015-11-07T19:29:00.000Z" }
    { "_id" : ObjectId("5665dc4ff485eb63b3e04bba"), "symbol" : ".XBT", "price" : 386.91, "timestamp" : "2015-11-07T19:30:00.000Z" }
    { "_id" : ObjectId("5665dc4ff485eb63b3e04bbb"), "symbol" : ".XBT", "price" : 387.08, "timestamp" : "2015-11-07T19:31:00.000Z" }
    { "_id" : ObjectId("5665dc4ff485eb63b3e04bbc"), "symbol" : ".XBT", "price" : 387.29, "timestamp" : "2015-11-07T19:32:00.000Z" }
    { "_id" : ObjectId("5665dc4ff485eb63b3e04bbd"), "symbol" : ".XBT", "price" : 387.29, "timestamp" : "2015-11-07T19:33:00.000Z" }
    { "_id" : ObjectId("5665dc4ff485eb63b3e04bbe"), "symbol" : ".XBT", "price" : 387.47, "timestamp" : "2015-11-07T19:34:00.000Z" }
    { "_id" : ObjectId("5665dc4ff485eb63b3e04bbf"), "symbol" : ".XBT", "price" : 387.42, "timestamp" : "2015-11-07T19:35:00.000Z" }
    { "_id" : ObjectId("5665dc4ff485eb63b3e04bc0"), "symbol" : ".XBT", "price" : 387.56, "timestamp" : "2015-11-07T19:36:00.000Z" }
    { "_id" : ObjectId("5665dc4ff485eb63b3e04bc1"), "symbol" : ".XBT", "price" : 387.77, "timestamp" : "2015-11-07T19:37:00.000Z" }
    { "_id" : ObjectId("5665dc4ff485eb63b3e04bc2"), "symbol" : ".XBT", "price" : 387.86, "timestamp" : "2015-11-07T19:38:00.000Z" }
    { "_id" : ObjectId("5665dc4ff485eb63b3e04bc3"), "symbol" : ".XBT", "price" : 387.91, "timestamp" : "2015-11-07T19:39:00.000Z" }
    { "_id" : ObjectId("5665dc4ff485eb63b3e04bc4"), "symbol" : ".XBT", "price" : 387.89, "timestamp" : "2015-11-07T19:40:00.000Z" }

I've tried to refactor my query using query builder syntax similar to here

    // Using the timestamp condition
    Indexes
    .find({})
    .where('symbol').equals(indexSymbol)
    .where('timestamp').gte(minDate)
    .sort('timestamp')
    .select('symbol price timestamp')
    .exec(function (err, items) {
        if(err) {
            console.log("Error: %s", err);
        } else {
            console.log("There are %s items", items.length); 
            // There are 0 items.
        }
    });

    // Without the timestamp condition
    Indexes
    .find({})
    .where('symbol').equals(indexSymbol)
    // .where('timestamp').gte(minDate)
    .sort('timestamp')
    .select('symbol price timestamp')
    .exec(function (err, items) {
        if(err) {
            console.log("Error: %s", err);
        } else {
            console.log("There are %s items", items.length);
            // There are 47425 items
        }
    });

I've also been trying to debug this by checking my schema from the query:

    var query = Indexes
    .find({'symbol':indexSymbol, 'timestamp': {$gte: minDate } })    
    .sort({'timestamp': 1});

    console.log("Query = \n %s", util.inspect(query.model.schema));

Which gives the following output:

        Query =
         { paths:
           { symbol:
              { enumValues: [],
                regExp: null,
                path: 'symbol',
                instance: 'String',
                validators: [],
                setters: [],
                getters: [],
                options: [Object],
                _index: null },
             price:
              { path: 'price',
                instance: 'Number',
                validators: [],
                setters: [],
                getters: [],
                options: [Object],
                _index: null },
             timestamp:
              { path: 'timestamp',
                instance: undefined,
                validators: [],
                setters: [],
                getters: [],
                options: [Object],
                _index: null,
                defaultValue: [Function: now] },
             _id:
              { path: '_id',
                instance: 'ObjectID',
                validators: [],
                setters: [Object],
                getters: [],
                options: [Object],
                _index: null,
                defaultValue: [Function: defaultId] },
             __v:
              { path: '__v',
                instance: 'Number',
                validators: [],
                setters: [],
                getters: [],
                options: [Object],
                _index: null } },
          subpaths: {},
          virtuals: { id: { path: 'id', getters: [Object], setters: [], options: {} } },
          nested: {},
          inherits: {},
          callQueue: [],
          _indexes: [],
          methods: {},
          statics: {},
          tree:
           { symbol: { type: [Function: String] },
             price: { type: [Function: Number] },
             timestamp: { default: [Function: now], type: [Function: Date] },
             _id: { auto: true, type: [Function: ObjectId] },
             id: { path: 'id', getters: [Object], setters: [], options: {} },
             __v: [Function: Number] },
          _requiredpaths: [],
          discriminatorMapping: undefined,
          _indexedpaths: undefined,
          options:
           { id: true,
             noVirtualId: false,
             _id: true,
             noId: false,
             read: null,
             shardKey: null,
             autoIndex: true,
             minimize: true,
             discriminatorKey: '__t',
             versionKey: '__v',
             capped: false,
             bufferCommands: true,
             strict: true,
             pluralization: true },
          _events: {} }

Questions:

1) Am I missing something for why my query would be working in MongoDB shell but not through Mongoose?

2) Is there something wrong with the 'date' field in my schema?

Answer 1

This was actually a really easy fix.

The 'timestamp' value was being saved as a String, and not a Date object.

I ran the following query from the MongoDB shell:

 db.indexes.find().forEach(function (doc) { doc.timestamp = new Date(Date.parse(doc.timestamp.toString())); db.indexes.save(doc); });

Which updated all my old records as Date's instead of String's and now the query works!