Couchdb Map function to get the data in between two dates

Question

I have set of docs in my couchdb here I mentioned one sample doc:

{
"_id": "26",
"_rev": "1-53ac67e9ec4b4ce8ffa9cd609e107aaf",
 "customer_name": "Vadilal",
"type": "trip",
"duration": "10 hours 27 mins",
"end_time": "Jan 1, 2014 10:11:00 PM",
"start_time": "Jan 11, 2014 8:46:00 AM",
}

If I pass timestamp(key) from the URL, if it is in between start_time and end_time then i want to get the docs.

Example:

suppose url would be like this

   .../trip/_design/trip/_view/trip?key="Jan 10, 2014 8:46:00 AM"

Here I am passing timestamp as Jan 10, 2014 8:46:00 AM so it comes in between start_time and end_time of above mentioned doc, in this case I need fetch the remaining information.

Kindly help to figure out this problem, it would be great help to me.

I have written function like below :

    function(doc){
    if(doc.type=="trip"){ 
   var startTime=new Date(doc.start_time);
   var endTime=new Date(doc.end_time);
  emit([startTime.getTime(),endTime.getTime()], doc);
    }

And calling URL as below :

../trip/_design/trip/_view/trip?startkey=[1390086890000]&endkey=[1390086890000,{}]

is above one correct according to my requirement???

Answer 1

That is unfortunately not possible. There are two problems with you requirement.

Views are arranged by a key

You could write a view like this to index documents by the start key or the end key respectively.

    function(doc){
      emit(doc.start_time, doc_id); // amend with end key to index by end key.
    }

you can use three parameters in your query:

• key - matches an exact key
• startkey - matches all documents that are greater or equal to the start key
• endkey - matches all documents that are greater or equal to the end key

The sorting problem

Depending on the type your index keys will be sorted either alphabetically or as integers. So if your view trip is using a start key like this:

    .../trip/_design/trip/_view/trip?start_key="Jan 10, 2014 8:46:00 AM"

You could return all values greater or equal to the string "Jan 10, 2014..." which would be true for "Jan 11, 2014" or "Jan 31, 2014" but there would be false positives for "July 22, 2011" or false negatives for "Feb 01, 2014" as F < J.

To solve this issue you will have to convert the start date to something that can be sorted in time order eg:

• parse the date to date time and then convert to epoch like How to get time in milliseconds since the unix epoch in Javascript?
• convert the date to yyyyMMddHHmmss formate like 20140110084600

Both of these will sort correctly.

Can you solve your problem

Yes, but with a little client side code. Here's the recipe:

• create two views trip/by_start_date and trip/by_end_date which return the start and end date as keys respectively. In a way that is sorted as per the second section above.
• Get two sets of documents.
• Set 1 should return all documents that start before your date: ..._view/by_start_date?endkey=[your_date]
• set 2 should return all documents that end after your date: ..._view/by_end_date?startkey=[your_date]
• You then have two sets of document ids and the result you were after will be those documents that are in both these sets.

Further optimization

In the above solution you might just get too many values returned to handle. You can further cut it down using CouchDB's ability to key on more than one value. Key by both start and end date like this:

    function(doc){
      var start_time = some_conversion_function(doc.start_time);
      var end_time = some_conversion_function(doc.end_time);
      emit([start_time, end_time], doc_id);
    }

If you have any more details on the maximum difference between start time and end time you can use this to further reduce the documents in each set. The below example will return the documents that start no later than X days before date and no later than date and end no later than X days after date.

    ..._view/by_start_date?startkey=[date-X,]&endkey=[date, date+X]

You can apply a similar logic to the by_end_date view.