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?

it would have been effective to have mentioned which nodejs library you were using, but based on the example it seems like its mongoosejs — Grimnoff, Apr 12 '19 at 15:15

score 599 · Accepted Answer · answered Jan 12 '12 at 13:28

599

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

answered Jan 12 '12 at 13:28

mnemosyn

45,391
6
76
82

14

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. – mnemosyn Jan 13 '12 at 09:51
Would the date initialization use slashes instead of dashes? like so: `new Date("2010/01/01");` – Victor S Mar 26 '15 at 20:46

Paul · Answer 2 · 2016-06-09T08:57:04.743

33

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!

edited Jun 09 '16 at 08:57

answered Apr 22 '16 at 08:28

Paul

499
4
6

".toISOString()" was a great help. – Howard Lee Sep 23 '22 at 20:04
With mongoose 5, it works fine without `.toISOString()` . – DBencz Mar 17 '23 at 14:03
Doesn't work with MongoDB (e.g. Atlas) as of today at least... Syntax error. OK with `IsoDate()` instead. – Fabien Haddadi Apr 04 '23 at 14:45

score 21 · Answer 3 · edited Aug 19 '22 at 09:22

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)

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. — Traderhut Games, Jun 18 '22 at 23:09

chovy · Answer 4 · 2017-01-14T09:06:54.007

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":

new Date().getTime() gives us current time in milliseconds
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).
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.

{ $gte: new Date(resultFromAbove).toISOString() } (mongodb .find() query)
Since we can't have variables we do it all in one shot: new Date(new Date().getTime()-60*5*1000)
...then convert to ISO string: .toISOString()
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.

I think you can use `Date.now()` instead of `new Date().getTime()` — Béranger, Jun 19 '17 at 12:25

score 12 · Answer 5 · answered Jun 26 '19 at 11:04

12

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

answered Jun 26 '19 at 11:04

Hamit YILDIRIM

4,224
1
32
35

1

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" – Fabien Haddadi Apr 04 '23 at 14:48
right ofcourse! – Hamit YILDIRIM Apr 18 '23 at 21:56

score 6 · Answer 6 · edited May 03 '18 at 23:59

6

You can also try:

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

edited May 03 '18 at 23:59

Quill

2,729
1
33
44

answered Jun 21 '16 at 16:08

Daniel_Madain

491
4
9

score 2 · Answer 7 · answered Oct 07 '18 at 15:19

2

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

answered Oct 07 '18 at 15:19

atulmy

1,364
15
23

Arthur Ronconi · Answer 8 · 2023-06-14T15:03:23.810

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

udit19PyJs · Answer 9 · 2021-04-19T15:50:11.937

-2

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

edited Apr 19 '21 at 15:50

answered Apr 19 '21 at 10:42

udit19PyJs

1
3

You shouldn't need to toString() the dates unless you are storing dates as strings. – Phil Mar 18 '22 at 09:29
instead of converting to ISOString you should use new keyword on ISODate – ADITYA HAZARIKA May 22 '22 at 17:22

return query based on date

9 Answers9

Linked