Help: Need N1ql Query to get last two weekdays data?

Hi Folks,
Please hep me with this query.

I need a n1ql query to get last two weekdays data.
If i run the query on Monday , it should fetch Monday and friday data. My date field is in epoch time.
If i run the query on Tuesday, It should fetch data of Tuesday and Monday.

Thank you

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);
1 Like

Hi ,

Thanks for the reply.

I created the index on my bucket using the
CREATE INDEX ix1 ON mybucket(epochtime);

Then my query is run from application.

Then i have to run the second query ?

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”)}] } )

and then the third query ?
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);

Could you explain please ?

There is one query (no two queries) it has CTE (WITH) query. see updated previous post

Thanks @vsr1 . The solution you shared works great.

And most probably I will be using this.

I’m just wondering , if you habe anyting on your mind by which this can be improved in anyway or optimized .Doesnt matter if there is nothing.

My requirement is just I have to get this data and save it in cache. So everyday my code pulls last two weekdays and save in cache at the start .

Thank you again for sharing the solution, appreciate it.

The following is simplified one

WITH today AS (NOW_STR("1111-11-11")),
     dateoffsets AS (CASE DATE_PART_STR(today,"dow")
                     WHEN 6 THEN [-2,-1,-1,0]
                     WHEN 0 THEN [-3,-2,-2,-1]
                     WHEN 1 THEN [-3,-2,0,1]
                     ELSE [-1,0,0,1]
                     END)
SELECT b.*
FROM mybucket AS b
WHERE (b.epochtime >= STR_TO_MILLIS(DATE_ADD_STR(today, dateoffsets[0], "day")) AND
               b.epochtime < STR_TO_MILLIS(DATE_ADD_STR(today, dateoffsets[1], "day")))
       OR (b.epochtime >= STR_TO_MILLIS(DATE_ADD_STR(today, dateoffsets[2], "day")) AND
               b.epochtime < STR_TO_MILLIS(DATE_ADD_STR(today, dateoffsets[3], "day")));

Other option is

SELECT b.*
FROM mybucket AS b
WHERE (b.epochtime >= $start1 AND b.epochtime < $end1)
               OR (b.epochtime >= $start2 AND b.epochtime < $end2)

From application supply $start1, $end1, $start2, $end2
except Monday all other days $start1, $start2 are same, $end1, $end2 same

You can even project document key using covered query below and use SDK asynchronously, parallel get the documents from KV.

SELECT RAW META(b).id
FROM mybucket AS b
WHERE (b.epochtime >= $start1 AND b.epochtime < $end1)
               OR (b.epochtime >= $start2 AND b.epochtime < $end2)

Hi @vsr1 , In the result from the query with CASE , Im only seeing todays records and not yesterday.

Today is friday , so I should see : Fri + Thur (yesterday ) right.
Im only getting Friday results

check the query (take below as i did some mistake one of the index) and data. The following gives me right result.
The query is much simplified

CREATE INDEX ix1 ON default(epochtime);

INSERT INTO default (KEY k , VALUE {"epochtime":STR_TO_MILLIS(k)})
       SELECT k FROM DATE_RANGE_STR(DATE_ADD_STR(NOW_STR(), -10, "day"), DATE_ADD_STR(NOW_STR(), 10, "day") ,"day") AS k;

WITH today AS (STR_TO_MILLIS(NOW_STR("1111-11-11"))),
     dateoffsets AS (CASE DATE_PART_MILLIS(today,"dow")
                     WHEN 6 THEN [-2,-1,-1,0]
                     WHEN 0 THEN [-3,-2,-2,-1]
                     WHEN 1 THEN [-3,-2,0,1]
                     ELSE [-1,0,0,1]
                     END)
SELECT b.*,  META(b).id
FROM default AS b
WHERE ((b.epochtime >= DATE_ADD_MILLIS(today, dateoffsets[0], "day") AND
               b.epochtime < DATE_ADD_MILLIS(today, dateoffsets[1], "day"))
      OR (b.epochtime >= DATE_ADD_MILLIS(today, dateoffsets[2], "day") AND
               b.epochtime < DATE_ADD_MILLIS(today, dateoffsets[3], "day")));

Hi @vsr1 , sorry to bother you.

I Found why im getting bad results . The DATE_PART_MILLIS is not working as expected

Here you see in the screenshots

image

That looks like seconds not milliseconds.

Try:

SELECT now_millis()

for the current millisecond time - you should see a much larger number.

As @dh mentioned number needs much bigger

Otherwise whole calculations will be wrong.

SELECT MILLIS_TO_STR(1659132089) AS d1, MILLIS_TO_STR(1659132089*1000) AS d2;
        "d1": "1970-01-19T20:52:12.089-08:00",
        "d2": "2022-07-29T15:01:29-07:00"

One you showing is seconds. Date functions expects milliseconds.
use 1659132089000
may be u want use NOW_UTC() vs NOW_STR().
If epochtime is seconds in data may want to divide by 1000 i.e. DATE_ADD_MILLIS()/1000

See if the following is easy to understand. (timestamp convert to date only and query on date)
If epochtime is in seconds change to MILLIS_TO_STR(epochtime*1000, “1111-11-11”) in index and query.

CREATE INDEX ix2 ON default(MILLIS_TO_STR(epochtime, "1111-11-11"));
WITH today AS (NOW_UTC("1111-11-11")),
     dates AS (CASE DATE_PART_STR(today,"dow")
               WHEN 6 THEN [DATE_ADD_STR(today, -2, "day"), DATE_ADD_STR(today, -1, "day")]
               WHEN 0 THEN [DATE_ADD_STR(today, -3, "day"), DATE_ADD_STR(today, -2, "day")]
               WHEN 1 THEN [DATE_ADD_STR(today, -3, "day"), today]
               ELSE [DATE_ADD_STR(today, -1, "day"), today]
               END)
SELECT b.*,  META(b).id
FROM default AS b
WHERE MILLIS_TO_STR(b.epochtime, "1111-11-11") IN dates;
1 Like

Hi @vsr1 , need suggestion .

The above solutions absolutely works if the data is in epoch millis . But now I have a problem.

The data in the Couchbase db is in epoch days and not in epoch seconds nor epoch millis .
Its epoch days which is being used.
Is there a way to get current time in epoch days instead of epoch Millis and make the above query work ?

To make the matter easy , the goal is to get only past records with no requirement of getting only weekdays. Now if the number given is two , we only get last two days records and not worry if its sat or sun. Its just todays and yesterdays data , even if its weekend , doesnt matter.

How could we modify the query ?

@Lenny ,

epochtime is epoch days

CREATE INDEX ix2 ON default(epochtime);
WITH today AS (NOW_UTC("1111-11-11")),
     epochday AS (IDIV(STR_TO_MILLIS(today),24*3600*1000)),
     dates AS (CASE DATE_PART_STR(today,"dow")
               WHEN 6 THEN [epochday-2, epochday-1]
               WHEN 0 THEN [epochday-3, epochday-2]
               WHEN 1 THEN [epochday-3, epochday]
               ELSE [epochday-1, epochday]
               END)
SELECT b.*,  META(b).id
FROM default AS b
WHERE b.epochtime IN dates;

Set named parameter $ndays or change to the constant

WITH epochday AS (IDIV(NOW_MILLIS(),24*3600*1000)),
     dates AS (ARRAY_RANGE(epochday+1-$ndays, epochday+1))
SELECT b.*,  META(b).id
FROM default AS b
WHERE b.epochtime IN dates;

Dates are continuous following is better

WITH epochday AS (IDIV(NOW_MILLIS(),24*3600*1000))
SELECT b.*,  META(b).id
FROM default AS b
WHERE b.epochtime BETWEEN epochday-$ndays+1 AND epochday;