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?

Solution

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.

Comments

[Prasad Silva]

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.