CODEWITHSUNDEEP
couchbasesql++
Issamovitch
Issamovitch

Reputation: 413

get all documents from previous month in couchbase server 5.1

I'm trying to get all documents from previous month in couchbase server 5.1.1.

I have a field dateCreation in timestamp

my query will be launched every month(M) at 15 day to retreive M-1 documents.

i use N1QL.

Thanks in advance.

Upvotes: 1

Views: 713

Answers (3)

vivekagarwal277
vivekagarwal277

Reputation: 585

Maybe this can also help:

SELECT * FROM bucket WHERE DATE_DIFF_STR(NOW_UTC(), dateCreation, 'month') <= 1

Upvotes: 0

EbenH
EbenH

Reputation: 566

To supplement vsr's answer, since you want all documents from the previous calendar month, try this:

SELECT * FROM bucket 
WHERE 
  dateCreation >= SUBSTR(DATE_ADD_STR(CLOCK_STR(), -1, 'month'),0,8) || "01" 
and 
  dateCreation < SUBSTR(CLOCK_STR(),0,8) || "01";

Upvotes: 1

vsr
vsr

Reputation: 7414

CREATE INDEX ix1 ON bucket(dateCreation);
SELECT * FROM bucket WHERE dateCreation >= DATE_ADD_STR(CLOCK_STR(), -1, 'month');

You can change CLOCK_STR() with constant or change as query parameter and supply the value.

Checkout DATE functions https://developer.couchbase.com/documentation/server/current/n1ql/n1ql-language-reference/datefun.html

Upvotes: 1

Related Questions