MongoDB/Mongoose querying at a specific date?

Question

Is it possible to query for a specific date ?

I found in the mongo Cookbook that we can do it for a range Querying for a Date Range Like that :

db.posts.find({"created_on": {"$gte": start, "$lt": end}})

But is it possible for a specific date ? This doesn't work :

db.posts.find({"created_on": new Date(2012, 7, 14) })

Wasteland

Answer 1

That should work if the dates you saved in the DB are without time (just year, month, day).

Chances are that the dates you saved were new Date(), which includes the time components. To query those times you need to create a date range that includes all moments in a day.

db.posts.find({ //query today up to tonight
    created_on: {
        $gte: new Date(2012, 7, 14), 
        $lt: new Date(2012, 7, 15)
    }
})

Pepillo

Remember that that in the Date() function, the month argument starts counting at 0, not 1. On the other hand, the days start counting at 1... details – Iberia

isnt it better to do _ 1 day to get next date rather than hard code both of them? – Franklinfranklinite

Is this method works fine if the date is stored like this:>>"date" : ISODate("2016-01-04T14:07:17.496Z") – Doityourself

Don't you have your months and date switches. Dates format should be: yyyy, dd, MM – Byrne

@Byrne stackoverflow.com/users/2529191/thethakuri When are they ever in yyyy, dd, MM order? Show where that is documented. – Washrag

Answer 2

...5+ years later, I strongly suggest using date-fns instead

import endOfDayfrom 'date-fns/endOfDay'
import startOfDay from 'date-fns/startOfDay'

MyModel.find({
  createdAt: {
    $gte: startOfDay(new Date()),
    $lte: endOfDay(new Date())
  }
})

For those of us using Moment.js

const moment = require('moment')

const today = moment().startOf('day')

MyModel.find({
  createdAt: {
    $gte: today.toDate(),
    $lte: moment(today).endOf('day').toDate()
  }
})

Important: all moments are mutable!

tomorrow = today.add(1, 'days') does not work since it also mutates today. Calling moment(today) solves that problem by implicitly cloning today.

Woodchopper

I suggest using .startOf('day') instead of .hours(0).minutes(0).seconds(0). So the first line would be: var today = moment().startOf('day') – Oxycephaly

If we're using moment(today) to clone the object, another solution is: var tomorrow = today.clone().add(1, 'days') – Letter

@Letter Explicit cloning rather than implicit, same result but perhaps easier to read indeed! – Woodchopper

How to do this in Nodejs? – Foothold

Alternatively, I could suggest the following for $lt : moment(today).endOf('day'). To prevent actual results from the next day being included. – Nth

for me moment(today).endOf('day') gives the same day with the time of: 23:59:59.999. So actually looks more correct to use $lte, otherwise objects at that particular time will be ignored. – Osbert

Thanks! You convinced me to add date-fns as a dependency! – Oswaldooswalt

Clean solution, but I think it is a little bit overkilled to add a 5.5Mb dependency for that – Boundless

Answer 3

Yeah, Date object complects date and time, so comparing it with just date value does not work.

You can simply use the $where operator to express more complex condition with Javascript boolean expression :)

db.posts.find({ '$where': 'this.created_on.toJSON().slice(0, 10) == "2012-07-14"' })

created_on is the datetime field and 2012-07-14 is the specified date.

Date should be exactly in YYYY-MM-DD format.

Note: Use $where sparingly, it has performance implications.

Departmentalism

I have used your answer it gives the error $where not allowed in this atlas tier how can solve it? – Egis

As an alternative you can use $expr and $function db.posts.find({ $expr: { $function: { body: function(created_on) { return created_on.toJSON().slice(0, 10) == "2012-07-14"' }, args: ["$created_on"], lang: "js" } } } ) – Katsuyama

If you are using the Atlas Cluster’s free M0, or shared M2 and M5 clusters (these do not support server side JavaScript), even with MongoDB versions 4.4, this feature is not available. – Katsuyama

Answer 4

Have you tried:

db.posts.find({"created_on": {"$gte": new Date(2012, 7, 14), "$lt": new Date(2012, 7, 15)}})

The problem you're going to run into is that dates are stored as timestamps in Mongo. So, to match a date you're asking it to match a timestamp. In your case I think you're trying to match a day (ie. from 00:00 to 23:59 on a specific date). If your dates are stored without times then you should be okay. Otherwise, try specifying your date as a range of time on the same day (ie. start=00:00, end=23:59) if gte doesn't work.

similar question

Capps

This will include all elements younger than the specified date, which is probably not what OP wanted. Consider adding a "lt" option like the other answers. – Bonita

I think if you have $gte instead of gte it would be better. – Inveteracy

This has already been answered more correctly above, but it was bugging me knowing that my answer was off, so I updated it to be correct for the question. Hey, it's been 4 years. lol – Capps

@MichaelDJohnson it will not work for the problem stated above, we need to make proper timestamp otherwise the result will not be according to expectations. – Apc

Answer 5

You can use following approach for API method to get results from specific day:

# [HTTP GET]
getMeals: (req, res) ->
  options = {}
  # eg. api/v1/meals?date=Tue+Jan+13+2015+00%3A00%3A00+GMT%2B0100+(CET)
  if req.query.date?
    date = new Date req.query.date
    date.setHours 0, 0, 0, 0
    endDate = new Date date
    endDate.setHours 23, 59, 59, 59
    options.date =
      $lt: endDate
      $gte: date

  Meal.find options, (err, meals) ->
      if err or not meals
        handleError err, meals, res
      else
        res.json createJSON meals, null, 'meals'

Whipple

Answer 6

Well a very simple solution to this is given below

const start = new Date(2020-04-01);
start.setHours(0, 0, 0, 0);
const end = new Date(2021-04-01);
end.setHours(23, 59, 59, 999);
queryFilter.created_at={
    $gte:start,
    $lte:end
}
YourModel.find(queryFilter)

So, the above code simply finds the records from the given start date to the given end date.

Apc

Answer 7

Problem I came into was filtering date in backend, when setting date to 0 hour, 0 minute, 0 second, 0 milisecond in node server it does in UTC time so current date 0 hour, 0 minute, 0 second, 0 milisecond of client may vary i.e. as a result which may gives a day after or before due to conversion of UTC time to local timezone

I fixed those by sending local time from client to server

 // If client is from Asia/Kathmandu timezone it will zero time in that zone.
// Note UTCDate time with zero time is not equal to above mention
const timeFromClient = new Date(new Date().setHours(0,0,0,0)).getTime()

And used this time to filter the documents by using this query

const getDateQuery = (filterBy, time) => {
    const today = new Date(time);
    const tomorrow = new Date(today.getDate() + 1);

    switch(filterBy) {
        case 'past':
            return {
                $exists: true,
                $lt: today,
            };
        case 'present': 
            return {
                $exists: true,
                $gte: today,
                $lt: tomorrow
            };
        case 'future':
            return {
                $exists: true,
                $gte: tomorrow
            };
        default: 
            return {
                $exists: true
            };
    };
};
const users = await UserModel.find({
    expiryDate: getDateQuery('past', timeFromClient)
})

This can be done in another approach using aggregate if we have timezoneId like Asia/Kathmandu

const getDateQuery = (filterBy) => {
    const today = new Date();
    const tomorrow = new Date(today.getDate() + 1);

    switch(filterBy) {
        case 'past':
            return {
                $exists: true,
                $lt: today,
            };
        case 'present': 
            return {
                $exists: true,
                $gte: today,
                $lt: tomorrow
            };
        case 'future':
            return {
                $exists: true,
                $gte: tomorrow
            };
        default: 
            return {
                $exists: true
            };
    };
};
await UserModel.aggregate([
   {
       $addFields: {
            expiryDateClientDate: {
                $dateToParts: {
                    date: '$expiryDate',
                    timezone: 'Asia/Kathmandu'
                }
            }
        },  
   },
   {
        $addFields: {
            expiryDateClientDate: {
                $dateFromParts: {
                    year: '$expiryDateClientDate.year',
                    month: '$expiryDateClientDate.month',
                    day: '$expiryDateClientDate.day'
                }
            }
        },
   },
   {
        $match: {
              expiryDateClientDate: getDateQuery('past')
        }            
   }
])

Goosander

Answer 8

i do it in this method and works fine

  public async getDatabaseorderbyDate(req: Request, res: Response) {
    const { dateQuery }: any = req.query
    const date = new Date(dateQuery)
    console.log(date)
    const today = date.toLocaleDateString(`fr-CA`).split('/').join('-')
    console.log(today)
    const creationDate = {
      "creationDate": {
        '$gte': `${today}T00:00:00.000Z`,
        '$lt': `${today}T23:59:59.999Z`
      }
    };

`
``

Cellulosic

Answer 9

We had an issue relating to duplicated data in our database, with a date field having multiple values where we were meant to have 1. I thought I'd add the way we resolved the issue for reference.

We have a collection called "data" with a numeric "value" field and a date "date" field. We had a process which we thought was idempotent, but ended up adding 2 x values per day on second run:

{ "_id" : "1", "type":"x", "value":1.23, date : ISODate("2013-05-21T08:00:00Z")}
{ "_id" : "2", "type":"x", "value":1.23, date : ISODate("2013-05-21T17:00:00Z")}

We only need 1 of the 2 records, so had to resort the javascript to clean up the db. Our initial approach was going to be to iterate through the results and remove any field with a time of between 6am and 11am (all duplicates were in the morning), but during implementation, made a change. Here's the script used to fix it:

var data = db.data.find({"type" : "x"})
var found = [];
while (data.hasNext()){
    var datum = data.next();
    var rdate = datum.date;
    // instead of the next set of conditions, we could have just used rdate.getHour() and checked if it was in the morning, but this approach was slightly better...
    if (typeof found[rdate.getDate()+"-"+rdate.getMonth() + "-" + rdate.getFullYear()] !== "undefined") {
       if (datum.value != found[rdate.getDate()+"-"+rdate.getMonth() + "-" + rdate.getFullYear()]) {
           print("DISCREPENCY!!!: " + datum._id + " for date " + datum.date);
       }
       else {
           print("Removing " + datum._id);
           db.data.remove({ "_id": datum._id});
       }
    }
    else {
       found[rdate.getDate()+"-"+rdate.getMonth() + "-" + rdate.getFullYear()] = datum.value;
    }
}

and then ran it with mongo thedatabase fixer_script.js

Sacrilege

Answer 10

Seemed like none of the answers worked for me. Although someone mentioned a little hint, I managed to make it work with this code below.

let endDate = startingDate
endDate = endDate + 'T23:59:59';

Model.find({dateCreated: {$gte: startingDate, $lte: endDate}})

startingDate will be the specific date you want to query with.

I preferred this solution to avoid installing moment and just to pass the startingDate like "2021-04-01" in postman.

Gauzy

Answer 11

Check wether the dates are stored as timestamps or not in mongodb. If not then simple query is enough. If stored as a timestamps then pass the start and end time as range.

Case 1: When dates are not stored as timestamps. Then use this simple way of querying.

// Pass the dates
const sales = await Sale.find({
      createdAt: {
        $gte: startDate , // Greater than or equal to startDate
        $lte: endDate, // Less than or equal to endDate
      },
    });

Case 2: When dates are stored as timestamps then pass the start and end hours also.

// Pass the dates
const sales = await Sale.find({
      createdAt: {
        $gte: `${startDate}T00:00:00` , // Greater than or equal to startDate
        $lte: `${endDate}T23:59:59`, // Less than or equal to endDate
      },
    });

Bocanegra