Format Couchbase Date format and retrieve group by date

Hi , I have requirement, My project moved from postgres to couchbase , have to get the count of items for the last 20days ,group by date and in desc order , but unable to format the date
I have is - “startDate”: “2020-10-09T00:00:00.000”
Tried using SUBSTR, DATE_PART(), DATE_FORMAT_STR() but no luck.
Postgres SQL:
select CAST(upd_dm AS date) as date, count(distinct(t8285_sbr_id)) as itemscount, from mytable where upd_dm > current_date - interval ‘20 days’ group by CAST(upd_dm AS date) ORDER BY CAST(upd_dm AS date) desc;
looking for something in couchbase

Tried: select startDate , count(*) as itemscount from mybucket AS d
LET startDate = DATE_FORMAT_STR(startDate,“1234-12-12”)
where startDate > ‘2020-09-20’ and startDate<‘2020-11-03’ group by startDate ORDER BY startDate desc

Requirement
Date Itemscount
2020-10- 31 20
2020-10- 30 34
2020-10-29 79
2020-20-28 46

select sd AS startDate , count(*) as itemscount 
FROM  `mybucket`  AS d
LET sd =DATE_FORMAT_STR(startDate, '1111-11-11')
where sd > ‘2020-09-20’ and sd <‘2020-11-03’ group by sd ORDER BY sd desc

or do SUBSTR(startDate,0,10)

1 Like

Thank you very much , it works., but instead of giving date range , is it possible for me to always pull last 20 days count as above mentioned in postgresSQL?

WHERE sd > DATE_ADD_STR(NOW_STR(“1111-11-11”),-20,“day”)