CODEWITHSUNDEEP
marklogicdate-range
MarkLogic Engineering
MarkLogic Engineering

Reputation: 11

Date/Element range index on MM-DD-YYYY format in MarkLogic

Have user data with timestamp in the below format and stored as string.

MM-DD-YYYYThh:mm:ss

Sample:

{
  "Name":"myname",
  "created":"12-17-2014T13:40:07"
}

I want to find all the documents created after a given timestamp. Element Range index with scalar type as date time does not work.

My basic requirement is to find all the documents created after the given time.

Would appreciate any help to meet the requirements

Upvotes: 1

Views: 55

Answers (3)

Mads Hansen
Mads Hansen

Reputation: 66783

You could create a TDE that parses that dateTime value and indexes it as xs:dateTime

Below is an example of how to do that:

'use strict'
const tde = require("/MarkLogic/tde.xqy");
const createdTDE = 
  {
    "template": {
      "context": "/created",
      "vars":[
        {
          "name":"myDateTime",
          "val":"xdmp:parse-dateTime('[M01]-[D01]-[Y0001]T[h01]:[m01]:[s01]', .)"
        }
      ],
      "rows": [
        {
          "schemaName": "test",
          "viewName": "created",
          "columns": [
            {
              "name": "created",
              "scalarType": "dateTime",
              "val": "$myDateTime"
            }
          ]
        }
      ]
    }
  }
tde.templateInsert(
  "/test/createdTDE.json" ,
  createdTDE,
  xdmp.defaultPermissions(),
  ["TDE"]
)

Once you have the TDE in place, you can use it to select where created is greater or less than.

cts.search(
  cts.columnRangeQuery("test", "created", "created", xs.dateTime('2014-12-17T13:40:07'), ">"), 
  "unfiltered"
)

Upvotes: 1

David Ennis -CleverLlamas.com
David Ennis -CleverLlamas.com

Reputation: 7770

As @rjrudin already mentioned, change the source data to match an xs:date-time format. If the source data cannot be changed, then there are a few alternatives:

  1. Brute force:
    • Iterate over documents, dynamically check. Not very efficient and not scalable.
  2. Metadata:
    • Use a script to farm the data and add it as a metadata value to the document in the proper format.
    • Then add a field to the metadata
    • Then add a range index
    • Use a range query
  3. Triple - a persisted functional index:
    • benefits from the following characteristics of a triple:
      • Linked to document fragment (in all recent versions of MarkLogic)
      • Already stored as a range index
    • Create a TDE template that transforms the date the the appropriate format
    • Store this as the object of a triple
    • Use cts.tripleRangeQuery() for search or op.fromSparql()/op.fromTriples() if using Optic API
  4. Same as (3) - but create a view with a column. Then use via Optic, SQL or cts.columnRangeQuery()

Upvotes: 1

rjrudin
rjrudin

Reputation: 2236

You need to change the order of the year/month/date - try the following in qconsole to see what's an accepted dateTime:

xs.dateTime("2024-12-17T13:40:07")

Upvotes: 0

Related Questions