从当前日期使用光学查询减去6个月的日期

发布于 2025-02-11 14:07:30 字数 913 浏览 1 评论 0原文

我试图根据使用MarkLogic中的OptiC查询来获得基于日期字段（预订日期）的最后6个月计数。

通过以下查询，预订日期小于当前日期时，我可以获得记录计数。我需要做的是从我的预订日期和此日期以后的记录计数的6个月大日期。我成功地在Xquery中创建了同样的东西。

但是，我无法从目前的Optic查询日期减去6个月大的日期。你能帮忙吗？

这是我正在使用的光学查询：

import module namespace op="http://marklogic.com/optic" at "/MarkLogic/optic.xqy";
import module namespace ofn="http://marklogic.com/optic/expression/fn" at "/MarkLogic/optic/optic-fn.xqy";
declare option xdmp:mapping "false";
op:from-view("GTM2_Shipment", "Shipment_View")
=> op:where(op:and((
                   op:eq(op:col("transMode"), 'Road'),op:gt(op:col("Ancillary_QuotePrice"), 0),op:lt(ofn:format-dateTime(op:col('BookingCreateDt'), '[Y0001]-[M01]-[D01]'),ofn:format-dateTime(fn:current-dateTime(),'[Y0001]-[M01]-[D01]'))

                   ))
                   )
=> op:group-by("transMode", op:count("QuotePrice", "Ancillary_QuotePrice"))
=> op:result()

原文

I am trying to get last 6 months count of a column value based on a date field (Booking Created Date) using optic query in MarkLogic.

With the below query , I am able to get the count of records when Booking date is less than the current date.
What I need to do is to get the 6 months old date from my current date in my Booking Date and count records beyond this date. I was successful in creating the same in xquery.

But I am not able to subtract the 6 months old date from my current date in optic query.
Can you please help.

Here is the optic query I am using :

import module namespace op="http://marklogic.com/optic" at "/MarkLogic/optic.xqy";
import module namespace ofn="http://marklogic.com/optic/expression/fn" at "/MarkLogic/optic/optic-fn.xqy";
declare option xdmp:mapping "false";
op:from-view("GTM2_Shipment", "Shipment_View")
=> op:where(op:and((
                   op:eq(op:col("transMode"), 'Road'),op:gt(op:col("Ancillary_QuotePrice"), 0),op:lt(ofn:format-dateTime(op:col('BookingCreateDt'), '[Y0001]-[M01]-[D01]'),ofn:format-dateTime(fn:current-dateTime(),'[Y0001]-[M01]-[D01]'))

                   ))
                   )
=> op:group-by("transMode", op:count("QuotePrice", "Ancillary_QuotePrice"))
=> op:result()

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

单身狗的梦 2025-02-18 14:07:30

您可以减去 p6m“）来自fn：current-datetime（）：

fn:current-dateTime() - xs:yearMonthDuration("P6M")

您当前正在比较格式化日期时间的字符串值，以大大多于。尽管它们确实在字符串上进行词典编分，但比较DateTime值更容易。

=> op:where(
     op:and((
       op:eq(op:col("transMode"), 'Road'),
       op:gt(op:col("Ancillary_QuotePrice"), 0),
       op:lt(op:col('BookingCreateDt'), fn:current-dateTime() - xs:yearMonthDuration("P6M"))
     ))
   )

You can subtract xs:yearMonthDuration("P6M") from the fn:current-dateTime():

fn:current-dateTime() - xs:yearMonthDuration("P6M")

Also, you are currently comparing the string value of the formatted dateTime for greater and less than. While they do sort lexicographically as strings, it's easier to just compare the dateTime values.

=> op:where(
     op:and((
       op:eq(op:col("transMode"), 'Road'),
       op:gt(op:col("Ancillary_QuotePrice"), 0),
       op:lt(op:col('BookingCreateDt'), fn:current-dateTime() - xs:yearMonthDuration("P6M"))
     ))
   )

回复收藏 0 原文

~没有更多了~