How to query between two date in Couchbase

Question

I have 3 documents which has a key called "updatedDateTime", I want the records between 2 date range of "updatedDateTime". Below are the documents:-

{
    "appId": "98b7dcbd-89b1-4d41-b951-28eec467218e",
    "updatedDateTime: "10/03/2023 19:08",
    "attributes": {
        "activity": "Adhoc Request",
        "comments": "Great Work",
        "assignedTo": "Sweta"
     }
    "status": "New",
    "taskId": "03775cc5-6ff5-4b02-89cd-160609b9c370"
},
{
    "appId": "98b7dcbd-89b1-4d41-b951-28eec467218e",
    "updatedDateTime: "21/03/2023 10:18",
    "attributes": {
        "activity": "Adhoc Request",
        "comments": "Great Work",
        "assignedTo": "Praveen"
     }
    "status": "New",
    "taskId": "03775cc5-6ff5-4b02-89cd-160609b9c380"
},
{
    "appId": "98b7dcbd-89b1-4d41-b951-28eec467218e",
    "updatedDateTime: "02/04/2023 5:00",
    "attributes": {
        "activity": "Supplier Migration Request",
        "comments": "Incomplete Work",
        "assignedTo": "Praveen"
     }
    "status": "New",
    "taskId": "03775cc5-6ff5-4b02-89cd-160609b9c490"
}

I have written a query to get the data on the basis of 2 date range but there is no result. Below is the query :-

SELECT workflow_update.* FROM workflow_update 
 WHERE 
   type='task' 
 AND
   appId='98b7dcbd-89b1-4d41-b951-28eec467218e' 
 AND 
   updatedDateTime 
 BETWEEN
   "11/01/2023 00:00" AND "08/05/2023 23:59" 
 limit 5

Result :-

[]

Expected Result :-

All the 3 documents

Any leads will be very helpful

Answer 1

Remember JSON has no native date type and as your timestamps are not in ISO format you have to explicitly convert them to millisecond values for use with BETWEEN. (If your dates were ISO format - i.e. YYYY-MM-DDTHH:MM:SS.FFF - your existing statement should work.)

To convert, use the conversion functions listed here https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/datefun.html

(Your version will dictate what conversion options are available.)

e.g.

SELECT workflow_update.* FROM workflow_update 
 WHERE
   appId='98b7dcbd-89b1-4d41-b951-28eec467218e'
 AND
   STR_TO_MILLIS(updatedDateTime,"02/01/2006 15:04")
 BETWEEN
       STR_TO_MILLIS("11/01/2023 00:00","02/01/2006 15:04")
   AND STR_TO_MILLIS("08/05/2023 23:59","02/01/2006 15:04")
 LIMIT 5

(I omitted the type="task" filter too since your example documents don't include that field.)

If you're on an earlier version (lacking format specification for the conversion functions), you may have to re-assemble the strings for conversion and (to simplify) use ISO constants for the filters, e.g.

SELECT workflow_update.* FROM workflow_update 
 LET time = SPLIT(SUBSTR(updatedDateTime,11),':')
    ,date = SPLIT(SUBSTR(updatedDateTime,0,10),'/')
 WHERE
   appId='98b7dcbd-89b1-4d41-b951-28eec467218e'
 AND
      STR_TO_MILLIS(date[2]||'-'||date[1]||'-'||date[0]||'T'
                 ||SUBSTR('0'||time[0],LENGTH(time[0])-1)||':'||time[1]||':00')
 BETWEEN
   STR_TO_MILLIS("2023-01-11T00:00:00") AND STR_TO_MILLIS("2023-05-08T23:59:59")
 LIMIT 5

To convert the field to ISO format you could use:

UPDATE workflow_update
SET updatedDateTime = millis_to_str(STR_TO_MILLIS(updatedDateTime,"02/01/2006 15:04"))
WHERE ...

which would then permit the statement:

SELECT workflow_update.* FROM workflow_update 
 WHERE
   appId='98b7dcbd-89b1-4d41-b951-28eec467218e'
 AND 
   updatedDateTime BETWEEN "2023-01-11T00:00" AND "2023-05-08T23:59"
 LIMIT 5

(ISO works without conversion as sorting the strings sorts in date order automatically thanks to the format.)

HTH.