Querying CouchDB documents between a start date and an end date

Question

I've been trying to figure out how to create a CouchDB view that will let me query all the documents that have a start date greater than A and an end date less than B.

Is this possible in CouchDB or another noSQL document store? Should I scrap it and go back to SQL?

I'm simply trying to do the SQL equivalent of:

SELECT * WHERE [start timestamp] >= doc.start AND [end timestamp] < doc.end;

Answer 1

Just create a map like this:

function (doc) {emit(doc.timestamp, 1)}

then query the view with:

?descending=true&limit=10&include_docs=true // Get the latest 10 documents

The view will be sorted oldest to latest so descending=true reverses that order.

If you want a specific range.

?startkey="1970-01-01T00:00:00Z"&endkey="1971-01-01T00:00:00Z"

would get you everything in 1970.

These should help:

• http://wiki.apache.org/couchdb/Introduction_to_CouchDB_views
• http://wiki.apache.org/couchdb/HttpViewApi
• http://wiki.apache.org/couchdb/View_collation

Answer 2

Use an array key in your map function

function (doc) {
  var key = [doc.start, doc.end]
  emit(key, doc)
}

Then to get documents with a start date greater then 1970-01-01T00:00:00Z and an end date before 1971-01-01T00:00:00Z use the query

?startkey=["1970-01-01T00:00:00Z", ""]&endkey=["\ufff0", "1971-01-01T00:00:00Z"]

Answer 3

I was looking for the same thing and stumbled upon this question. With CouchDB 2.0 or higher you have the possibility of using Mango Queries, which includes greater-than and less-than.

A mango query could look like:

"selector": {
   "effectiveDate": {
      "$gte": "2000-04-29T00:00:00.000Z",
      "$lt": "2020-05-01T00:00:00.000Z"
   }
}

Answer 4

Use startkey and endkey. This way you can decide your date range at runtime without slowing down your query.