How to fetch records after a particular date

Question

I am trying to fetch the records that has the "effectiveDateOfAction" field greater than Oct'2017. Please find the below 3 records.

{
 "_id": "TRAN001",
 "_rev": "13-59a53069c1ebd6ecfc23ca1dea0ba28f",
 "effectiveDateOfAction": "10-30-2018",
 "employeeName": "Kumar,Vinoth",
 "transferReportID": "TRAN001",
 "~version": "76:0"
}

{
 "_id": "TRAN001",
 "_rev": "12-c320c61168f5d6d020f971124cb395f2",
 "effectiveDateOfAction": "05-10-2018",
 "employeeName": "Vinoth",
 "transferReportID": "TRAN002",
 "~version": "77:0"
}

{
 "_id": "TRAN003",
 "_rev": "16-567a15e9ea7e2349d4c24816e7eafda3",
 "effectiveDateOfAction": "10-20-2017",
 "employeeName": "Kumar",
 "transferReportID": "TRAN003",
 "~version": "78:0"
}

Please find my query below which i tried.I am checking using Project Fauxton.

{"selector": {"$and": [{"transferReportID": {"$ne": null}},{"effectiveDateOfAction": {"$gt": "10-31-2017"}}]}}

Please help me getting the correct query.

Answer 1

As there is no native date type in JSON, it's important to store dates in a format that makes sense at query time. The "Month-Day-Year" format may be useful when rendering dates for a US audience but it makes little sense for querying.

I would suggest the "YYYY-MM-DD" format e.g "2018-10-30". This stores the same data as before but the sort order happens to be in date order, because years are longer than months and months are longer than days.

You can then use a query using the "$gte" operator:

{
  "selector": {
    "effectiveDateOfAction": {
      "$gte": "2018-10-01"
     }
  }
}

This reads as "fetch documents whose 'effectiveDateOfAction' field is greater than or equal to 1st October 2018'.

See this blog post on how to store and query dates in CouchDB.

Answer 2

If possible, change your date format to a sortable form, as Glyn Bird said. I'd suggest to use ISO_8601, this is preferd for JSON (e.g. Javascript Date.toJSON).

If you can't change your data, you can create a view, which transforms your dates into a sortable format.

Example: Put a design doc similar to the following to your database

{
  _id: '_design/employees',
  views: {
    by_action_date: {
      map: "function (doc) {\n        if (doc.effectiveDateOfAction && doc.employeeName) { // filter for employee docs\n          var dt = doc.effectiveDateOfAction.split('-'); // parse your date format\n          emit(`${dt[2]}-${dt[1]}-${dt[0]}`); // emit iso date as key\n        }\n      }"
    }
  }
}

The map function has to be given as string in the document, formatted it is:

function(doc) {
    if (doc.effectiveDateOfAction && doc.employeeName) { // filter for employee docs
      var dt = doc.effectiveDateOfAction.split('-'); // parse your date format
      emit(`${dt[2]}-${dt[1]}-${dt[0]}`); // emit iso date as key
    }
  }

You can then query it to get your employees sorted:
Use include_docs = true param to get your real documents included.

/my-database/_design/employees/_view/by_action_date?include_docs=true

You then can also use startkey and endkey params, to limit to a specific time frame:

/my-database/_design/employees/_view/by_action_date?include_docs=true&startkey="2018-10-01"&endkey="2018-10-31"

This will return your TRAN001 and TRAN002 documents.