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