CODEWITHSUNDEEP
couchbasesql++
Mahadev Shinde
Mahadev Shinde

Reputation: 119

Couchbase n1ql query for weekly range

I am trying to get query based on weekly range.

document structure :

{
    type: "city",
    createdDate: "2020-08-15T12:00:00"
    //other fields
}

Query should count records per week.

output:

date         count
2020-08-01  20
2020-08-08  20

It should display for weekly in the current month

Upvotes: 1

Views: 363

Answers (1)

vsr
vsr

Reputation: 7414

Checkout Date functions https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/datefun.html

SELECT date, COUNT(1) AS cnt
FROM mybucket AS d
LET date = DATE_PART_STR(DATE_ADD_STR(d.createdDate, -IMOD(DATE_PART_STR(d.createdDate,"day")-1,7)), "1111-11-11")
WHERE ...........
GROUP BY date;

Note: Last week of the month is partial. Weekly grouping per month is complex because no equal days. Checkout Extracting date components in above link (iso_week, week)

Upvotes: 3

Related Questions