How to query date range on the MongoDB collection where the ISO date is stored in string field?

Question

Scenario: Consider I am having a collection called MyCollection, with following data:

{
    "_id" : 'MyUniqueID_01'
    "CreatedTime" : "2013-12-01T14:35:00Z",
    "LastModifiedTime" : "2013-12-01T13:25:00Z"
}

Now I want to query the MongoDB database where the above mentioned kind of data is in huge number of documents. And my query is based on date range i.e. using $gt, $gte, $lt & $lte

So my query may be something like:

db.MyCollection.find({ 'CreatedTime': {$gt: '2013-05-25T09:29:40.572Z'}})

Considering the above examples the expected result is, query has to get a document (since the "CreatedTime" : "2013-12-01T14:35:00Z" is greater than value passed in query '2013-05-25T09:29:40.572Z'); whereas it not, the issue is that field CreatedTime is in string format.

Question: Is there any way so that I can get my expected result perfectly without changing the string field type to date?

Randa

ISO 8601 date-time strings like these should work fine for date range queries. That's one of the advantages of the format. Your example query does return your example doc, as expected. – Agility

@Agility : Thanks... Exactly It works on my local DB but the same query is failing do the expected on the live server.. Is there any version dependencies? – Randa

@AmolMKulkarni Out of curiosity, did you find out why it didn't work on the server? I'm working on querying between time ranges and came across this while searching. – Junkie

@Raghuveer: Nope.. :( I did not get the reason for why it failed on server. For your information server was CentOS & MongoDB Version 2.0.9 – Randa

I have mono version +2.6 and having bumped into the same issue, when I tried to put my date ranges as ` ISODate("2014-08-17T20:59:59.000Z")` it worked. – Synchromesh

Answer 1

You can make the queries exactly as you did in the example.

The string ordering is consistent and will give you the exact relationship you want.

Screens

Yes, but I would also add that the query string must use the same timezone suffix as the data set (which also adds another constraint on how you're storing the data strings) because you're doing a string comparison. For example, the OP's example is in ISO 8601 UTC form (with the 'Z' suffix), therefore the query string must also be in that form. – Velocipede