Try this. To test it various dates change today AS (“2022-07-28”) different 7 days
Adjust bucket and epochtime filed based on your bucket and field names
Create the following index
CREATE INDEX ix1 ON mybucket(epochtime);
Sample data
INSERT INTO mybucket VALUES (UUID(), {"epochtime":STR_TO_MILLIS("2022-07-29T10:12:15")}),
VALUES (UUID(), {"epochtime":STR_TO_MILLIS("2022-07-28T10:12:15")}),
VALUES (UUID(), {"epochtime":STR_TO_MILLIS("2022-07-26T10:12:15")}),
VALUES (UUID(), {"epochtime":STR_TO_MILLIS("2022-07-25T10:12:15")}),
VALUES (UUID(), {"epochtime":STR_TO_MILLIS("2022-07-24T10:12:15")}),
VALUES (UUID(), {"epochtime":STR_TO_MILLIS("2022-07-23T10:12:15")}),
VALUES (UUID(), {"epochtime":STR_TO_MILLIS("2022-07-22T10:12:15")});
Run the following query from application or query workbench or cbq shell or using curl
WITH today AS (NOW_STR("1111-11-11")),
dow AS (TO_STR(DATE_PART_STR(today,"dow"))),
daterange AS ( {
"0": [{"start": DATE_ADD_STR(today, -3, "day"),
"end": DATE_ADD_STR(today, -1, "day")}],
"1": [{"start": DATE_ADD_STR(today, -3, "day"),
"end": DATE_ADD_STR(today, -2, "day")},
{"start": DATE_ADD_STR(today, 0, "day"),
"end": DATE_ADD_STR(today, 1, "day")} ],
"2": [{"start": DATE_ADD_STR(today, -1, "day"),
"end": DATE_ADD_STR(today, 1, "day")}],
"3": [{"start": DATE_ADD_STR(today, -1, "day"),
"end": DATE_ADD_STR(today, 1, "day")}],
"4": [{"start": DATE_ADD_STR(today, -1, "day"),
"end": DATE_ADD_STR(today, 1, "day")}],
"5": [{"start": DATE_ADD_STR(today, -1, "day"),
"end": DATE_ADD_STR(today, 1, "day")}],
"6": [{"start": DATE_ADD_STR(today, -2, "day"),
"end": DATE_ADD_STR(today, 0, "day")}] } )
SELECT b.* FROM daterange.[dow] AS d
INNER JOIN mybucket AS b ON b.epochtime >= STR_TO_MILLIS(d.start) AND b.epochtime < STR_TO_MILLIS(d.end);