How to retrieve records from Cloudant DB over date range

Question

I am storing following object in CloudantDB.

 SystemLog: 
     {
     "logContext":"ARTLogging",
     "logTime":2016-05-04T22:33:02Z,
     "logReference":"CloudantARTManager",
     "log":"Hello world. Test Message",
     "status":"Success"
     }

The cloudantdb has following index:

 {
  "name":"SysLogIdx1",
  "ddoc":"SysLogIdx1",
  "index":{"fields":
                [
                  {"logContext":"asc"},
                  {"logTime":"asc"},
                  {"logReference":"asc"}
                 ]
          }
}

I am trying to retreive these logs over 2 mins range with a filter as follows

 "selector": {
            "logContext": "ARTLogging", 
            "logTime": {"$gt": "2016-05-04T22:32:02Z"}, 
            "logTime": {"$lt": "2016-05-04T22:34:02Z"}, 
            "logReference": "CloudantARTManager"
            }

I also tried with this

"selector": 
    {"logContext": "ARTLogging", 
     "logTime": {"$gt": "2016-05-04T22:32:02Z", "$lt": "2016-05-04T22:34:02Z"}, 
     "logReference": "CloudantARTManager"
    }

The db.find is not picking any records. But If I remove logTime filter I am getting records properly. What am I doing wrong.

Thanks Madhu

score 0 · Answer 1 · answered May 06 '16 at 21:33

It looks like you can not perform a range on a date field. So I created another field in my SystemLog as follows:

  long logLongTime = logTime.getTime(); // here logTime is a java.util.Date.

Then I added an index field as shown:

db.createIndex("SysLogIdx2", "SysLogIdx2", null, new IndexField[]{
                       new IndexField("logContext",SortOrder.asc),
                       new IndexField("logLongTime",SortOrder.asc),
                       new IndexField("logReference",SortOrder.asc)});

Next I updated my selector string as follows:

"selector": 
        {"logContext": "ARTLogging", 
        "logLongTime": {"$gte": 1462569436000, "$lte": 1462569556000}, 
        "logReference": "CloudantARTManager"
        }

Then the db.find retrieved correct records.

Thanks Madhu

How to retrieve records from Cloudant DB over date range

1 Answers1