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?

Answer 1

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.

Answer 2

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

Answer 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.

Answer 4

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.

Answer 5

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

Answer 6

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.