Find objects between two dates MongoDB

Question

I've been playing around storing tweets inside mongodb, each object looks like this:

{
"_id" : ObjectId("4c02c58de500fe1be1000005"),
"contributors" : null,
"text" : "Hello world",
"user" : {
    "following" : null,
    "followers_count" : 5,
    "utc_offset" : null,
    "location" : "",
    "profile_text_color" : "000000",
    "friends_count" : 11,
    "profile_link_color" : "0000ff",
    "verified" : false,
    "protected" : false,
    "url" : null,
    "contributors_enabled" : false,
    "created_at" : "Sun May 30 18:47:06 +0000 2010",
    "geo_enabled" : false,
    "profile_sidebar_border_color" : "87bc44",
    "statuses_count" : 13,
    "favourites_count" : 0,
    "description" : "",
    "notifications" : null,
    "profile_background_tile" : false,
    "lang" : "en",
    "id" : 149978111,
    "time_zone" : null,
    "profile_sidebar_fill_color" : "e0ff92"
},
"geo" : null,
"coordinates" : null,
"in_reply_to_user_id" : 149183152,
"place" : null,
"created_at" : "Sun May 30 20:07:35 +0000 2010",
"source" : "web",
"in_reply_to_status_id" : {
    "floatApprox" : 15061797850
},
"truncated" : false,
"favorited" : false,
"id" : {
    "floatApprox" : 15061838001
}

How would I write a query which checks the created_at and finds all objects between 18:47 and 19:00? Do I need to update my documents so the dates are stored in a specific format?

Oops, I want to query the created_at and find all between two dates. — Tom, May 31 '10 at 16:23
I am curious that why not use timestamp, any advantages by using of the Date Obj? — Leo, Jun 03 '16 at 10:25
@Leo The biggest advantage with the Date object over milliseconds since the epoch or whatever is human readability. In this case, setting your start range to `2010-04-29T00:00:00.000Z` is much easier than calculating the same date/time in milliseconds. You can also do time zone conversion pretty easily. Also, Dates already handle things like leap days, leap seconds, and other oddities that you usually don't want to handle yourself. — Thunderforge, Jan 23 '17 at 19:22

score 832 · Accepted Answer · edited Sep 02 '16 at 13:54

832

~~Querying for a Date Range (Specific Month or Day)~~ in the ~~MongoDB Cookbook~~ has a very good explanation on the matter, but below is something I tried out myself and it seems to work.

items.save({
    name: "example",
    created_at: ISODate("2010-04-30T00:00:00.000Z")
})
items.find({
    created_at: {
        $gte: ISODate("2010-04-29T00:00:00.000Z"),
        $lt: ISODate("2010-05-01T00:00:00.000Z")
    }
})
=> { "_id" : ObjectId("4c0791e2b9ec877893f3363b"), "name" : "example", "created_at" : "Sun May 30 2010 00:00:00 GMT+0300 (EEST)" }

Based on my experiments you will need to serialize your dates into a format that MongoDB supports, because the following gave undesired search results.

items.save({
    name: "example",
    created_at: "Sun May 30 18.49:00 +0000 2010"
})
items.find({
    created_at: {
        $gte:"Mon May 30 18:47:00 +0000 2015",
        $lt: "Sun May 30 20:40:36 +0000 2010"
    }
})
=> { "_id" : ObjectId("4c079123b9ec877893f33638"), "name" : "example", "created_at" : "Sun May 30 18.49:00 +0000 2010" }

In the second example no results were expected, but there was still one gotten. This is because a basic string comparison is done.

edited Sep 02 '16 at 13:54

Dinei

4,494
4
36
60

answered May 31 '10 at 13:04

ponzao

20,684
3
41
58

3

Looks interesting, but does the stored date need to be in a specific format. I've just been storing what was provided by twitter, does this need to be changed into a different format? – Tom May 31 '10 at 16:24
9

You've probably stored the timestamps as strings, so I am guessing MongoDB won't realize that they are in fact dates. Thus doing a range query on them would result in an alphabetical range query (e.g. "Jan Mon 01.01.2010" being before "Jan Sun 01.01.1000"). It would probably make sense to format all date data into the MongoDB format, which I think is just plain JavaScript Date. – ponzao May 31 '10 at 16:47
2

I just used this to convert my strings to date objects http://stackoverflow.com/questions/2900674/how-do-i-convert-a-property-in-mongodb-from-text-to-date-type – Tom May 31 '10 at 21:07
Okay cool! I'd guess the range queries mentioned in the cookbook should work then, did you try them out already? – ponzao May 31 '10 at 21:26
Yep once I was storing the dates correct the cookbook examples worked as expected. – Tom Jun 15 '10 at 08:46
If the date is store as string (it's very common) then you should be able to extract the string, converted to DateTime `new Date(dt_field);` and stored in a temporal JavaScript array with a `mongo_collection.foreach()` and `array.push()` and then query that array. NOTE: use temporal mongo table/documents for large amount of data. – Jaider May 04 '15 at 15:47
What should i do if i want to use a variable instead of a hard coded date for e.g. instead of 'created_at: { $gte: ISODate("2010-04-29T00:00:00.000Z"), $lt: ISODate("2010-05-01T00:00:00.000Z") }' I am using a date filter and want something like : 'created_at: { $gte: from_date, $lt: to_date }' but my query is defined inside a meteor helper and my variables are created inside template events (I am using Meteorjs by the way). – Rahul Khatri Jun 05 '15 at 06:54

arcseldon · Answer 2 · 2014-07-30T15:34:18.623

50

To clarify. What is important to know is that:

Yes, you have to pass a Javascript Date object.
Yes, it has to be ISODate friendly
Yes, from my experience getting this to work, you need to manipulate the date to ISO
Yes, working with dates is generally always a tedious process, and mongo is no exception

Here is a working snippet of code, where we do a little bit of date manipulation to ensure Mongo (here i am using mongoose module and want results for rows whose date attribute is less than (before) the date given as myDate param) can handle it correctly:

var inputDate = new Date(myDate.toISOString());
MyModel.find({
    'date': { $lte: inputDate }
})

edited Jul 30 '14 at 15:34

answered Jul 30 '14 at 15:21

arcseldon

35,523
17
121
125

1

Plus one for clarity. If your using moment in the backend, it still retains the toISOString() function. I use moment to add and subtract time for my queries. – VocoJax Feb 28 '19 at 15:41
I think it should read: var inputDate = new Date(myDate).toISOString(); – Ben in CA Jun 20 '23 at 21:15

score 31 · Answer 3 · answered Jan 16 '18 at 13:24

31

Python and `pymongo`

Finding objects between two dates in Python with pymongo in collection posts (based on the tutorial):

from_date = datetime.datetime(2010, 12, 31, 12, 30, 30, 125000)
to_date = datetime.datetime(2011, 12, 31, 12, 30, 30, 125000)

for post in posts.find({"date": {"$gte": from_date, "$lt": to_date}}):
    print(post)

Where {"$gte": from_date, "$lt": to_date} specifies the range in terms of datetime.datetime types.

answered Jan 16 '18 at 13:24

Anton Tarasenko

8,099
11
66
91

1

This is not working. Whenever I run this query I get by default complete response and not filtered response – Abhay Bh Apr 23 '20 at 07:51
2

This is not working. I tried with python3 and it gets no results back – hellbreak Mar 03 '21 at 10:53

score 22 · Answer 4 · edited Sep 07 '17 at 01:01

22

db.collection.find({"createdDate":{$gte:new ISODate("2017-04-14T23:59:59Z"),$lte:new ISODate("2017-04-15T23:59:59Z")}}).count();

Replace collection with name of collection you want to execute query

edited Sep 07 '17 at 01:01

aldoWan

93
1
6

answered Apr 26 '17 at 14:53

GSK

553
5
11

2

@DanDascalescu -may be it did not add anything but what is your concern here ? – GSK Jul 23 '18 at 09:20
33

Duplicate answers waste people's time. – Dan Dascalescu Jul 23 '18 at 19:43
1

@DanDascalescu - then don't waste time , do something productive. – GSK Nov 02 '22 at 13:24

score 21 · Answer 5 · answered Jun 03 '10 at 20:22

MongoDB actually stores the millis of a date as an int(64), as prescribed by http://bsonspec.org/#/specification

However, it can get pretty confusing when you retrieve dates as the client driver will instantiate a date object with its own local timezone. The JavaScript driver in the mongo console will certainly do this.

So, if you care about your timezones, then make sure you know what it's supposed to be when you get it back. This shouldn't matter so much for the queries, as it will still equate to the same int(64), regardless of what timezone your date object is in (I hope). But I'd definitely make queries with actual date objects (not strings) and let the driver do its thing.

score 15 · Answer 6 · edited May 17 '18 at 08:52

15

Use this code to find the record between two dates using $gte and $lt:

db.CollectionName.find({"whenCreated": {
    '$gte': ISODate("2018-03-06T13:10:40.294Z"),
    '$lt': ISODate("2018-05-06T13:10:40.294Z")
}});

edited May 17 '18 at 08:52

sɐunıɔןɐqɐp

3,332
15
36
40

answered May 17 '18 at 07:41

Sunil Pal

167
1
4

What does this add to the accepted answer, provided 8 years earlier? – Dan Dascalescu Jul 22 '18 at 22:42

score 14 · Answer 7 · answered Dec 05 '18 at 02:50

Using with Moment.js and Comparison Query Operators

  var today = moment().startOf('day');
  // "2018-12-05T00:00:00.00
  var tomorrow = moment(today).endOf('day');
  // ("2018-12-05T23:59:59.999

  Example.find(
  {
    // find in today
    created: { '$gte': today, '$lte': tomorrow }
    // Or greater than 5 days
    // created: { $lt: moment().add(-5, 'days') },
  }), function (err, docs) { ... });

score 9 · Answer 8 · edited Mar 11 '20 at 11:01

9

You can also check this out. If you are using this method, then use the parse function to get values from Mongo Database:

db.getCollection('user').find({
    createdOn: {
        $gt: ISODate("2020-01-01T00:00:00.000Z"),
        $lt: ISODate("2020-03-01T00:00:00.000Z")
    }
})

edited Mar 11 '20 at 11:01

Shredator

940
3
17
32

answered Mar 11 '20 at 08:20

Kevin007

163
1
3

score 9 · Answer 9 · answered Jun 23 '20 at 18:26

db.collection.find({$and:
  [
    {date_time:{$gt:ISODate("2020-06-01T00:00:00.000Z")}},
     {date_time:{$lt:ISODate("2020-06-30T00:00:00.000Z")}}
   ]
 })

##In case you are making the query directly from your application ##

db.collection.find({$and:
   [
     {date_time:{$gt:"2020-06-01T00:00:00.000Z"}},
     {date_time:{$lt:"2020-06-30T00:00:00.000Z"}}
  ]

 })

score 9 · Answer 10 · answered Apr 30 '22 at 17:10

9

Save created_at date in ISO Date Format then use $gte and $lte.

db.connection.find({
    created_at: {
        $gte: ISODate("2010-05-30T18:47:00.000Z"),
        $lte: ISODate("2010-05-30T19:00:00.000Z")
    }
})

answered Apr 30 '22 at 17:10

Sahil Thummar

1,926
16
16

KARTHIKEYAN.A · Answer 11 · 2018-11-20T13:37:04.257

6

use $gte and $lte to find between date data's in mongodb

var tomorrowDate = moment(new Date()).add(1, 'days').format("YYYY-MM-DD");
db.collection.find({"plannedDeliveryDate":{ $gte: new Date(tomorrowDate +"T00:00:00.000Z"),$lte: new Date(tomorrowDate + "T23:59:59.999Z")}})

edited Nov 20 '18 at 13:37

answered Dec 07 '17 at 12:53

KARTHIKEYAN.A

18,210
6
124
133

1

Slight typo in your answer $gte not $get :) – Bob Nov 20 '18 at 12:57
1

Sorry i answered very tired condition, so i made mistake. Thanks for your help to update my answer. you did good job :) @Bob – KARTHIKEYAN.A Nov 20 '18 at 13:39

score 5 · Answer 12 · answered Jul 26 '19 at 15:36

mongoose.model('ModelName').aggregate([
    {
        $match: {
            userId: mongoose.Types.ObjectId(userId)
        }
    },
    {
        $project: {
            dataList: {
              $filter: {
                 input: "$dataList",
                 as: "item",
                 cond: { 
                    $and: [
                        {
                            $gte: [ "$$item.dateTime", new Date(`2017-01-01T00:00:00.000Z`) ]
                        },
                        {
                            $lte: [ "$$item.dateTime", new Date(`2019-12-01T00:00:00.000Z`) ]
                        },
                    ]
                 }
              }
           }
        }
     }
])

Gabriel Guérin · Answer 13 · 2023-02-06T15:58:45.833

For those using Make (formerly Integromat) and MongoDB: I was struggling to find the right way to query all records between two dates. In the end, all I had to do was to remove ISODate as suggested in some of the solutions here.

So the full code would be:

"created": {
    "$gte": "2016-01-01T00:00:00.000Z",
    "$lt": "2017-01-01T00:00:00.000Z"
}

This article helped me achieve my goal.

UPDATE

Another way to achieve the above code in Make (formerly Integromat) would be to use the parseDate function. So the code below will return the same result as the one above :

"created": {
    "$gte": "{{parseDate("2016-01-01"; "YYYY-MM-DD")}}",
    "$lt": "{{parseDate("2017-01-01"; "YYYY-MM-DD")}}"
}

⚠️ Be sure to wrap {{parseDate("2017-01-01"; "YYYY-MM-DD")}} between quotation marks.

score 3 · Answer 14 · edited Mar 26 '14 at 14:24

3

Convert your dates to GMT timezone as you're stuffing them into Mongo. That way there's never a timezone issue. Then just do the math on the twitter/timezone field when you pull the data back out for presentation.

edited Mar 26 '14 at 14:24

Chris Seymour

83,387
30
160
202

answered Jan 16 '14 at 15:45

heregear

67
2

ZacharyST · Answer 15 · 2015-08-04T18:17:01.553

3

Why not convert the string to an integer of the form YYYYMMDDHHMMSS? Each increment of time would then create a larger integer, and you can filter on the integers instead of worrying about converting to ISO time.

edited Aug 04 '15 at 18:17

answered Jun 09 '15 at 03:26

ZacharyST

658
2
6
22

1

Because time isn't just happening in my local timezone. – Michael Cole Dec 09 '16 at 17:32
2

This becomes a nightmare once you start having to convert the time to and from that format everywhere. If you're going to do something like this at the very least use the return value from .getTime() from a JS date object. – nikk wong Apr 10 '17 at 21:49
1

thats why we always store data in UTC – codewandler Jul 10 '17 at 21:12

score 1 · Answer 16 · answered Sep 06 '21 at 08:26

Scala: With joda DateTime and BSON syntax (reactivemongo):

val queryDateRangeForOneField = (start: DateTime, end: DateTime) =>
    BSONDocument(
      "created_at" -> BSONDocument(
        "$gte" -> BSONDateTime(start.millisOfDay().withMinimumValue().getMillis), 
        "$lte" -> BSONDateTime(end.millisOfDay().withMaximumValue().getMillis)),
     )

where millisOfDay().withMinimumValue() for "2021-09-08T06:42:51.697Z" will be "2021-09-08T00:00:00.000Z" and where millisOfDay(). withMaximumValue() for "2021-09-08T06:42:51.697Z" will be "2021-09-08T23:59:99.999Z"

score 0 · Answer 17 · answered Dec 04 '13 at 11:29

i tried in this model as per my requirements i need to store a date when ever a object is created later i want to retrieve all the records (documents ) between two dates in my html file i was using the following format mm/dd/yyyy

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

<html>
<head>

    <script>
//jquery
    $(document).ready(function(){  
    $("#select_date").click(function() { 
    $.ajax({
    type: "post",
    url: "xxx", 
    datatype: "html",
    data: $("#period").serialize(),  
    success: function(data){
    alert(data);
    } ,//success

    }); //event triggered

    });//ajax
    });//jquery  
    </script>

    <title></title>
</head>

<body>
    <form id="period" name='period'>
        from <input id="selecteddate" name="selecteddate1" type="text"> to 
        <input id="select_date" type="button" value="selected">
    </form>
</body>
</html>

in my py (python) file i converted it into "iso fomate" in following way

date_str1   = request.POST["SelectedDate1"] 
SelectedDate1   = datetime.datetime.strptime(date_str1, '%m/%d/%Y').isoformat()

and saved in my dbmongo collection with "SelectedDate" as field in my collection

to retrieve data or documents between to 2 dates i used following query

db.collection.find( "SelectedDate": {'$gte': SelectedDate1,'$lt': SelectedDate2}})

Find objects between two dates MongoDB

17 Answers17

Python and `pymongo`

Linked

Related

Find objects between two dates MongoDB

17 Answers17

Python and pymongo

Linked

Related

Python and `pymongo`