Search code examples
xquerymarklogicmarklogic-optic-api

MarkLogic Optic API: Filtering rows with op:where() with timestamps older than 30 days

In my dataset I have a field testDateTime that contains xs:dateTime timestamps: 2022-06-02T10:42:00Z

Using the optic API and op:where(), is there a way I can filter my results to exclude any rows with timestamps older than 30 days?

I have been attempting to use the following SQL statements in op:like-sql-condition() but do not look to be supported:

=>op:where(op:sql-condition("MyData.testDateTime < CURRENT_DATE - 30"))
=>op:where(op:sql-condition("MyData.testDateTime < DATEADD(day, -30, GETDATE())"))
=>op:where(op:sql-condition("MyData.testDateTime < NOW() - INTERVAL 30 DAY"))
=>op:where(op:sql-condition("MyData.testDateTime < GETDATE() - 30"))

Is there a way to get this working either with a SQL condition or with optic operators themselves?

Solution

  • What about just using op:lt() and testing whether the testDateTime column is less than currentDateTime() - xs:dayTimeDuration("P30D"):

    => op:where(op:lt(op:col("MyData.testDateTime"), current-dateTime() - xs:dayTimeDuration("P30D")))