How to subtract a date from current date after converting the date into ISO format in Couchbase

Question

I have a document where I have to get the number of days between two dates. Below is the document :-

{
  "appId": "f6e9f71c-d1b6-45c1-a2f3-e5b3a668646c",
  "dueDate": "23/5/2023"
}

I wanted to get the difference between current date and the dueDate. So my current date will be in ISO format. Any idea how to convert the dueDate into ISO format so that I can subtract and get the remaining days

I have tried the below query...

SELECT (DATE_FORMAT_STR(date_add_str(now_str(), 0, "day"), '%d/%m/%Y') - attributes.dueDate) 
AS remainingDays
FROM workflow_update WHERE appId="f6e9f71c-d1b6-45c1-a2f3-e5b3a668646c";

Result :-

{ "remainingDays": null }

Expected Result :-

{ "remainingDays": 48 }

Assuming current date is = 10-07-2023

Any leads will be very helpful.

Answer 1

Try using DATE_DIFF_MILLIS or DATE_DIFF_STR for getting the difference between two dates. Here's an example (I've just hardcoded the dates in the SELECT via LET):

SELECT
  todaysDate,
  earlierDate,
  DATE_DIFF_MILLIS(earlierDate, todaysDate, 'day') AS remainingDays

LET
  todaysDateParse = DATE_FORMAT_STR(date_add_str(now_str(), 0, "day"), '%d/%m/%Y'),
  todaysDate = STR_TO_MILLIS(todaysDateParse, "DD/MM/YYYY"),
  earlierDate = STR_TO_MILLIS("23/5/2023", "DD/MM/YYYY")

This outputs:

[
  {
    "earlierDate": 1684800000000,
    "remainingDays": -48,
    "todaysDate": 1688947200000
  }
]

If you want a positive number, you can flip the arguments around in DATE_DIFF_MILLIS.

Some notes:

• I'm actually not clear on the difference between "%d/%m/%Y" and "DD/MM/YYYY", so you may want to double check that. As far as I can tell, using "DD/MM/YYYY" is correct, but I could be wrong.

• You can probably also simplify your DATE_FORMAT_STR line to DATE_FORMAT_STR(now_str(), '%d/%m/%Y'), since you are just adding 0, but I assume you had that there for a reason, so I left it in my example.