Querying CouchDB documents between a start date and an end date

16
votes

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;

4
datecouchdbnosql
Duplicate, see stackoverflow.com/questions/3079040/range-keys-in-couchdb-views - b_erb
@PartlyCloudy I don't see how this question is a dupe of your question. Your question generates a bunch of events at regular intervals over some range, whereas this is looking to find all the values between some range. I could just be using your solution incorrectly but I don't think so. - jfenwick

4 Answers

8
votes

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:

3
votes

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"]
2
votes

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"
   }
}
0
votes

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