Find objects between two dates MongoDB

MongodbDatetimeTwitterMongodb Query

Mongodb Problem Overview

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?

Mongodb Solutions

Solution 1 - Mongodb

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.

Solution 2 - Mongodb

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

Solution 3 - Mongodb

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.

Solution 4 - Mongodb

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.

Solution 5 - Mongodb

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

Solution 6 - Mongodb

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

Solution 7 - Mongodb

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) { ... });

Solution 8 - Mongodb

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

Solution 9 - Mongodb

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"}}
  ]

 })

Solution 10 - Mongodb

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

Solution 11 - Mongodb

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.

Solution 12 - Mongodb

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.

Solution 13 - Mongodb

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`) ]
                        },
                    ]
                 }
              }
           }
        }
     }
])

Solution 14 - Mongodb

For those who use 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 the 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.

Solution 15 - Mongodb

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"

Solution 16 - Mongodb

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

Solution 17 - Mongodb

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}})
Categories
Recommended Mongodb Solutions
Recommended Datetime Solutions
Recommended Twitter Solutions
Recommended Mongodb Query Solutions

Previous Solution:

How can I clear the NuGet package cache using the command line?

NugetNuget Package

Next Solution:

What is %2C in a URL?

UrlUrlencode

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionTomView Question on Stackoverflow
Solution 1 - MongodbponzaoView Answer on Stackoverflow
Solution 2 - MongodbarcseldonView Answer on Stackoverflow
Solution 3 - MongodbAnton TarasenkoView Answer on Stackoverflow
Solution 4 - MongodbBen SmithView Answer on Stackoverflow
Solution 5 - MongodbGSKView Answer on Stackoverflow
Solution 6 - MongodbSunil PalView Answer on Stackoverflow
Solution 7 - MongodbTính Ngô QuangView Answer on Stackoverflow
Solution 8 - MongodbKevin007View Answer on Stackoverflow
Solution 9 - MongodbJosé Pablo CeballosView Answer on Stackoverflow
Solution 10 - MongodbKARTHIKEYAN.AView Answer on Stackoverflow
Solution 11 - MongodbZacharySTView Answer on Stackoverflow
Solution 12 - MongodbheregearView Answer on Stackoverflow
Solution 13 - MongodbJitendraView Answer on Stackoverflow
Solution 14 - MongodbGabriel GuérinView Answer on Stackoverflow
Solution 15 - MongodbRoman KazanovskyiView Answer on Stackoverflow
Solution 16 - MongodbSahil ThummarView Answer on Stackoverflow
Solution 17 - Mongodbayu for uView Answer on Stackoverflow