英文:
How to subtract a date from current date after converting the date into ISO format in Couchbase
问题
{
"appId": "f6e9f71c-d1b6-45c1-a2f3-e5b3a668646c",
"dueDate": "23/5/2023"
}
我有一个文档,需要计算两个日期之间的天数。以下是文档内容:
我想要计算当前日期和dueDate之间的差值。所以我的当前日期将以ISO格式表示。有没有办法将dueDate转换为ISO格式,以便我可以进行减法运算并得到剩余的天数?
我尝试了以下查询...
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";
结果:
{ "remainingDays": null }
预期结果:
{ "remainingDays": 48 }
假设当前日期是= 10-07-2023
任何线索都将非常有帮助。
英文:
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.
答案1
得分: 1
尝试使用 DATE_DIFF_MILLIS
或 DATE_DIFF_STR
来获取两个日期之间的差异。以下是一个示例(我只是在 SELECT
中通过 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")
这将输出:
[
{
"earlierDate": 1684800000000,
"remainingDays": -48,
"todaysDate": 1688947200000
}
]
如果你想要一个正数,你可以在 DATE_DIFF_MILLIS
中颠倒参数的顺序。
一些注意事项:
-
我实际上不清楚 "%d/%m/%Y" 和 "DD/MM/YYYY" 之间的区别,所以你可能需要仔细检查一下。据我所知,使用 "DD/MM/YYYY" 是正确的,但我可能错了。
-
你可能还可以简化你的
DATE_FORMAT_STR
行为DATE_FORMAT_STR(now_str(), '%d/%m/%Y')
,因为你只是添加了0,但我假设你之前有一些原因放在那里,所以我在示例中保留了它。
英文:
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 toDATE_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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论