Query with string date format in mongodb

Question

I have a data like below in my mongoDB collection. I want to count the total number or search_term on today basis.

_id:ObjectId("5b7e1d38981cdc1a7c8bb5fc")
search_term:"Baiyoke Boutique"
date:"August 23rd 2018, 9:34:32 am"

_id:ObjectId("5b7e1fa8fa27fd2754080ad9")
search_term:"Baiyoke Boutique"
date:"August 23rd 2018, 9:44:56 am"

_id:ObjectId("5b7e28314d2d0f388c1596cd")
search_term:"Baiyoke Florting Market"
date:"August 23rd 2018, 10:21:21 am"

I have tried following query. I have used moment. I am not what mistake I did.

var start = moment().startOf('day');
var end = moment().endOf('day');

history.find({
    date: {
        $gte: start,
        $lt: end
    }
    }).count().toArray(
    function (e, res) {
        if (e) callback(e)
        else callback(null, res)
});

I get history.find(...).count(...).toArray is not a function — S S
– S S, Commented Aug 23, 2018 at 10:39

Ashh · Accepted Answer · 2019-11-12 11:49:26Z

14

You can try below queries in mongodb 3.6 and above

db.collection.find({
  "$expr": {
    "$gte": [{ "$dateFromString": { "dateString": "$date" }}, start.toDate() ],
    "$lt": [{ "$dateFromString": { "dateString": "$date" }}, end.toDate() ]
  }
}).count()

or with aggregation

db.collection.aggregate([
  { "$addFields": {
    "date": {
      "$dateFromString": {
        "dateString": "$date"
      }
    }
  }},
  { "$match": { "date": { "$gte": start.toDate(), "$lt": end.toDate() }}},
  { "$count": "count" }
])

edited Nov 12, 2019 at 11:49

answered Aug 23, 2018 at 11:24

Ashh

46.6k16 gold badges111 silver badges137 bronze badges

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

2 Comments

Naisarg Parmar Over a year ago

Hi @Ashh. Can you please help me. if my collection column have date="2020-12-13 17:20:00" and I want to fetch record greater than my current datetime which is "2020-12-11 00:00:00". how I can write expr in find()?

Ashh Over a year ago

@NaisargParmar Pls ask a new question i will definitely try to help

Senthur Deva · Accepted Answer · 2018-08-23 10:58:19Z

2

You can try this

db.col.aggregate([{$addFields: {
      convertedDate: { $toDate: "$date" }
   }},
   {"$match" : 
       {"convertedDate" : 
           {"$gte" : ISODate("2018-08-23T09:34:32.000Z"),
            "$lte" : ISODate("2018-08-23T09:34:32.000Z")}
        }
    },
    {"$group" : {"_id" : null,"count" : {"$sum" : 1}}},
    {"$project" : {"_id" : 0}}
   ])

This is for Node js

var start = moment().startOf('day');
var end = moment().endOf('day');

history.aggregate([{
    $addFields: {
        convertedDate: { $toDate: "$date" }
    }
},
{
    "$match":
    {
        "convertedDate":
        {
            "$gte": start,
            "$lte": end
        }
    }
},
{ "$group": { "_id": null, "count": { "$sum": 1 } } },
{ "$project": { "_id": 0 } }
], function (err, count) {
    console.log(count)
})

answered Aug 23, 2018 at 10:58

Senthur Deva

7975 silver badges12 bronze badges

5 Comments

Senthur Deva Over a year ago

what is your mongodb version?

S S Over a year ago

AggregationCursor { pool: null, server: null, disconnectHandler: Store { s: { storedOps: [], storeOptions: [Object], topology: [Object] }, length: [Getter] }, bson: BSON {}, ns: 'ogin-test.history', cmd: { aggregate: 'history', pipeline: [ [Object], [Object], [Object], [Object] ], cursor: {} }, options: { readPreference: ReadPreference { mode: 'primary', tags: undefined },

Senthur Deva Over a year ago

This code is working fine for me and $toDate supports mongo version 4+

S S Over a year ago

my mongodb version is 3.6.4

Wernfried Domscheit Over a year ago

Should be better var start = moment().startOf('day').toDate(); and var end = moment().endOf('day').toDate();

mehta-rohan · Accepted Answer · 2018-08-23 10:45:36Z

0

Try:

history.count({date: {
    $gte: start,
    $lt: end
}}, function( err, count){
    console.log( "Number of users:", count );
})

for native mongodb driver

db.collection(collectionName).count({}, function(error, numOfDocs){
    if(error) return callback(error);
    db.close();
    callback(null, numOfDocs);
});

edited Aug 23, 2018 at 10:45

answered Aug 23, 2018 at 10:43

mehta-rohan

1,3411 gold badge14 silver badges20 bronze badges

3 Comments

S S Over a year ago

the result is coming 0 .. it should give 3 according to above example data set

mehta-rohan Over a year ago

How do you insert your date? String or date?

mehta-rohan Over a year ago

wrap start and end with ISODate -- {date: { $gte: ISODate(start), $lt: ISODate(end) }

Shant Marouti · Accepted Answer · 2018-08-23 10:53:31Z

-1

You are storing the string representation of the date. Try calling toDate() on moment object before storing into the database

answered Aug 23, 2018 at 10:53

Shant Marouti

1,3258 silver badges11 bronze badges

1 Comment

Ray Foss Over a year ago

It is not necessary if you want to keep the JSON a compatible. You can get the benefits of using an EJSON Date object by formatting the data as ISO8601 with your validation. {"lastAccess": { $gt: "2021-08-01T00:00:00.000Z }}" will work. The OP is using ObjectID, so that doesn't seem to be his goal, so obviously something is wrong with the design.

Collectives™ on Stack Overflow

Query with string date format in mongodb

4 Answers 4

2 Comments

5 Comments

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

5 Comments

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related