MongoDB - return query based on date

Question

I have a data like this in mongodb

{ 
    "latitude" : "", 
    "longitude" : "", 
    "course" : "", 
    "battery" : "0", 
    "imei" : "0", 
    "altitude" : "F:3.82V", 
    "mcc" : "07", 
    "mnc" : "007B", 
    "lac" : "2A83", 
    "_id" : ObjectId("4f0eb2c406ab6a9d4d000003"), 
    "createdAt" : ISODate("2012-01-12T20:15:31Z") 
}

How do I query db.gpsdatas.find({'createdAt': ??what here??}), so that it returns the above data result to me from the db?

Doucet

it would have been effective to have mentioned which nodejs library you were using, but based on the example it seems like its mongoosejs – Wilfredwilfreda

Answer 1

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") });

Gyimah

Ahh :) I thought you were using the Mongo console. There, ISODate is a wrapper for the js Date object. Try something like "createdAt" : new Date("2010-01-01"). For some reason, that code does not work (for v. 2.0.2) in the Mongo console, however. – Gyimah

Would the date initialization use slashes instead of dashes? like so: new Date("2010/01/01"); – Chanel

Answer 2

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!

Eley

".toISOString()" was a great help. – Incorporating

With mongoose 5, it works fine without .toISOString() . – Rigveda

Doesn't work with MongoDB (e.g. Atlas) as of today at least... Syntax error. OK with IsoDate() instead. – Curarize

You should avoid to compare a Date value with a string. Better compare a Date value with a Date, thus { $gte: new Date(dateVar) } would be better. – Ding

Answer 3

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)

Therapeutics

I would point out that you lose the last second there, what you really want is to var dateNextDate = startDate.AddDays(1) And then do a lt on that date. – Benjie

Answer 4

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.

Benzel

I think you can use Date.now() instead of new Date().getTime() – Clingstone

Answer 5

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))
      } 
    })

Idealist

Hint: for inequalities: "greater than", "lower than", there's no need to describe HH:MM:SS and timezone if what you really want is midnight: you can then omit "T00:00:00.000Z" – Curarize

right ofcourse! – Idealist

Answer 6

You can also try:

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

Metaphase

Answer 7

If you are using Mongoose,

try {
  const data = await GPSDatas.aggregate([
    {
      $match: { createdAt : { $gt: new Date() }
    },
    {
      $sort: { createdAt: 1 }
    }
  ])
  console.log(data)

} catch(error) {
    console.log(error)
}

Plenty

Answer 8

Personally, I prefer to avoid usage of client-side javascript processing / 3rd party library like moment.js to avoid inconsistency of timezone processing between client side and server side.

1. range-based query

db.collection.find({
  "createdAt": {
    $gte: ISODate("2012-01-12T00:00:00Z"),
    $lt: ISODate("2012-01-13T00:00:00Z")
  }
})

2. constructed date range with $dateFromParts

db.collection.find({
  $expr: {
    $gte: [
      "$createdAt",
      {
        "$dateFromParts": {
          "year": 2012,
          "month": 1,
          "day": 12
        }
      }
    ]
  }
})

3. get range with date difference(example is 15 years before current date)

db.collection.find({
  $expr: {
    $gte: [
      "$createdAt",
      {
        "$dateSubtract": {
          "startDate": "$$NOW",
          "unit": "year",
          "amount": 15
        }
      }
    ]
  }
})

Galling

Answer 9

If you are using mongodb aggregation, then here is the code:

1. Here i am converting date which is dd/mm/yyyy to ISO Date (bcoz date is stored in ISO Date format in db)

const dateToIso=(from, to)=>{
    return {
        fromIsoDate: moment(from,"DD/MM/YYYY").toISOString(),
        toIsoDate: moment(to,"DD/MM/YYYY").add(1,'days').toISOString()
    }
}

2. finally these values using mongodb aggregation:

{
                $match:{
                    $and:[
                        {
                            createdAt:{
                                $lte: new Date(toIsoDate)
                            }
                        },
                        {
                            createdAt:{
                                $gte: new Date(fromIsoDate)
                            }
                        },
                        
                    ]
                }
            },

Impediment

You should avoid to compare a Date value with a string. Better use moment(from, "DD/MM/YYYY").toDate(), then you compare Date values with Date. – Ding

Answer 10

Whenever you have to deal with Date values, then I suggest a 3rd party Date library, like moments.js, Luxon, or Day.js

  {
    $match: {
      createdAt: { $gte: DateTime.fromISO(date).toJSDate() }
    }
  }

or createdAt: { $gte: moment().toDate() }

For plain Date values, it does not make any difference, i.e. there you can use native new Date(...) or ISODate(...). However, when you have to work with time zones or other math (e.g. beginOf('hour'), endOf('week'), add({days:7,months:1}), etc. then these libraries are much easier to use and better predicable than the native JavaScript Date objects.

Ding

Answer 11

I like to use reversed dates:

{ 
    ...
    "createdAt" : 120112201531
}

In this case the date format is "YYMMDDHHmmsss" (2-digits year + 2-digits month + 2-digits day + 2-digits hours + 2-digits minutes + 2-digits seconds).

Then you can do search like:

db.gpsdatas.find({"createdAt" : { $gte : 120112201531 }})

It's much more readable and easy to programming. If you want to use date filters without the time, it can even better because you can use a index type to improve performance with a date format like "YYYYMMDD".

To render this string format to date object in JavaScript you can use a module like DayJS with the CustomParseFormat plugin:

const date1 = dayjs('120112201531', 'YYMMDDHHmmsss')
const date2 = dayjs('20120112', 'YYYYMMDD')

https://day.js.org/docs/en/plugin/custom-parse-format

Caledonian

Oh my good, did you never hear about the Year-2000-Bug??? – Ding

Ok, but this is meant to work with dates greater than 2000, like a "timestamp." If you need a more comprehensive date, simply use the full date. It's simple. – Caledonian

Answer 12

If you want to get a record from a datetime, you can use the following:

(mongodbCompass 3.6.8 with PyMongo)

mongo.db.collection.aggregate([{"$match":{"object.object.key":{"$gte":ISODate('2021-01-27T00:00:00Z').toISOString()}}}])

db.collection.find({"object.object.key":{"$gte":ISODate('2021-01-27T00:00:00Z').toISOString()}})

Aspa

You shouldn't need to toString() the dates unless you are storing dates as strings. – Cashbook

instead of converting to ISOString you should use new keyword on ISODate – Dorcy