如何在将日期转换为ISO格式后从当前日期中减去日期,在Couchbase中。

huangapple go评论54阅读模式
英文:

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_MILLISDATE_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 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.

huangapple
  • 本文由 发表于 2023年7月11日 01:03:58
  • 转载请务必保留本文链接:https://go.coder-hub.com/76655891.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定