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;
Use startkey and endkey. This way you can decide your date range at runtime without slowing down your query.
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 1971.
These should help:
http://wiki.apache.org/couchdb/Introduction_to_CouchDB_views