Couchbase N1QL query data between two days without time

0
votes

I want to retrieve data from a Couchbase bucket with N1QL between two days (from 00:00:00.000 start date time until 23:59:59.999 end date time)

Is it enough to provide the dates without time, like this:

SELECT c.*
FROM customer c
WHERE c.start BETWEEN '2017-10-09' AND '2017-10-10'

Or do I need to provide the time explicitly:

SELECT c.*
FROM customer c
WHERE c.start BETWEEN '2017-10-09T00:00:00.000Z' AND '2017-10-10T23:59:59.999Z')

?

2
couchbasen1ql

2 Answers

0
votes

Couchbase date in ISO-8601 are string comparable. The both queries are fine. Checkout more details https://developer.couchbase.com/documentation/server/current/n1ql/n1ql-language-reference/datefun.html

0
votes

You need to use a view with a map function that convert date to Array like this :

function (doc, meta) {
    emit(dateToArray(doc.start), doc);
}

and then request the view with the startKey and endKey filter

Exemple with NodeJS client use the range method:

const viewQuery = couchbase.ViewQuery.from(nameOfYourDesignDocument, nameOfYourView);

bucket.query(viewQuery.range([2017,10,9], [2017,10,10]), (error, rows) => {
});