Subtract 6 months date from the current date using Optic Query

Question

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()

Mads Hansen · Answer 1 · 2022-06-29T12:30:20.377

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"))
     ))
   )

Subtract 6 months date from the current date using Optic Query

1 Answers1