Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

return query based on date

Tags:

mongodb

People also ask

How do you use dates in queries?

Correct syntax for dates in the Query function Per the Query Language documentation, we need to include the date keyword and ensure that the date is in the format yyyy-mm-dd to use a date as a filter in the WHERE clause of our Query function.

How does MongoDB compare only dates?

For example, if MongoDB stores July 7, 2015 with any time, I want to match that day only with today's date. In MySQL, I would do SELECT col1, col2 FROM table WHERE DATE(date_field) = DATE(NOW()); Notice how MySQL has a function to change the date_field to the date only during matching.

What is GTE MongoDB?

$gte selects the documents where the value of the field is greater than or equal to (i.e. >= ) a specified value (e.g. value .) For most data types, comparison operators only perform comparisons on fields where the BSON type matches the query value's type.


You probably want to make a range query, for example, all items created after a given date:

db.gpsdatas.find({"createdAt" : { $gte : new ISODate("2012-01-12T20:15:31Z") }});

I'm using $gte (greater than or equals), because this is often used for date-only queries, where the time component is 00:00:00.

If you really want to find a date that equals another date, the syntax would be

db.gpsdatas.find({"createdAt" : new ISODate("2012-01-12T20:15:31Z") });

Just been implementing something similar in Mongo v3.2.3 using Node v0.12.7 and v4.4.4 and used:

{ $gte: new Date(dateVar).toISOString() }

I'm passing in an ISODate (e.g. 2016-04-22T00:00:00Z) and this works for a .find() query with or without the toISOString function. But when using in an .aggregate() $match query it doesn't like the toISOString function!


if you want to get items anywhere on that date you need to compare two dates

You can create two dates off of the first one like this, to get the start of the day, and the end of the day.

var startDate = new Date(); // this is the starting date that looks like ISODate("2014-10-03T04:00:00.188Z")

startDate.setSeconds(0);
startDate.setHours(0);
startDate.setMinutes(0);

var dateMidnight = new Date(startDate);
dateMidnight.setHours(23);
dateMidnight.setMinutes(59);
dateMidnight.setSeconds(59);

### MONGO QUERY

var query = {
        inserted_at: {
                    $gt:morning,
                    $lt:dateScrapedMidnight
        }
};

//MORNING: Sun Oct 12 2014 00:00:00 GMT-0400 (EDT)
//MIDNIGHT: Sun Oct 12 2014 23:59:59 GMT-0400 (EDT)

If you want to get all new things in the past 5 minutes you would have to do some calculations, but its not hard...

First create an index on the property you want to match on (include sort direction -1 for descending and 1 for ascending)

db.things.createIndex({ createdAt: -1 }) // descending order on .createdAt

Then query for documents created in the last 5 minutes (60 seconds * 5 minutes)....because javascript's .getTime() returns milliseconds you need to mulitply by 1000 before you use it as input to the new Date() constructor.

db.things.find({
        createdAt: {
            $gte: new Date(new Date().getTime()-60*5*1000).toISOString()
         }
     })
     .count()

Explanation for new Date(new Date().getTime()-60*5*1000).toISOString() is as follows:

First we calculate "5 minutes ago":

  1. new Date().getTime() gives us current time in milliseconds
  2. We want to subtract 5 minutes (in ms) from that: 5*60*1000 -- I just multiply by 60 seconds so its easy to change. I can just change 5 to 120 if I want 2 hours (120 minutes).
  3. new Date().getTime()-60*5*1000 gives us 1484383878676 (5 minutes ago in ms)

Now we need to feed that into a new Date() constructor to get the ISO string format required by MongoDB timestamps.

  1. { $gte: new Date(resultFromAbove).toISOString() } (mongodb .find() query)
  2. Since we can't have variables we do it all in one shot: new Date(new Date().getTime()-60*5*1000)
  3. ...then convert to ISO string: .toISOString()
  4. new Date(new Date().getTime()-60*5*1000).toISOString() gives us 2017-01-14T08:53:17.586Z

Of course this is a little easier with variables if you're using the node-mongodb-native driver, but this works in the mongo shell which is what I usually use to check things.


Find with a specific date:

db.getCollection('CollectionName').find({"DepartureDate" : new ISODate("2019-06-21T00:00:00.000Z")})

Find with greater gte or little lt :

db.getCollection('CollectionName').find({"DepartureDate" : { $gte : new ISODate("2019-06-11T00:00:00.000Z") }})

Find by range:

db.getCollection('CollectionName').find({ 
    "DepartureDate": { 
        $lt: new Date(), 
        $gte: new Date(new Date().setDate(new Date().getDate()-15))
      } 
    })

You can also try:

{
    "dateProp": { $gt: new Date('06/15/2016').getTime() }
}