Search code examples
sqldatetimemarklogic

MarkLogic dateTime using SQL

I am currently trying to get a ODBC Connection with a Postgresql Driver underneath Hibernate running and ran into following problem.

This is how one of my TDE schema look:

<tde:template xmlns:tde='http://marklogic.com/xdmp/tde'
              xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'
              xsi:schemaLocation='http://marklogic.com/xdmp/tde'>
  <tde:context>/mytable</tde:context>
  <tde:enabled>true</tde:enabled>
  <tde:rows>
    <tde:row>
      <tde:schema-name>myschema</tde:schema-name>
      <tde:view-name>myview</tde:view-name>
      <tde:columns>
        <tde:column>
          <tde:name>mycolumn</tde:name>
          <tde:scalar-type>dateTime</tde:scalar-type>
          <tde:val>mycolumn</tde:val>
        </tde:column>
      </tde:columns>
    </tde:row>
  </tde:rows>
</tde:template>

Note that the column mycolumn is of type dateTime. Values look like this one: 2018-04-02T09:05:30

The datetimes contain a 'T' and are as of xs:dateTime('2018-04-02T09:05:30') a valid xs:dateTime.

A select query (select mycolumn from mytable) successfully returns values from this table.

But as soon as i try to read values from this table using a PostgresSql Driver, this exception gets thrown:

Caused by: java.lang.NumberFormatException: Trailing junk on timestamp: 'T09:05:30'
    at org.postgresql.jdbc.TimestampUtils.parseBackendTimestamp(TimestampUtils.java:345) ~[postgresql-42.2.2.jar:42.2.2]
    at org.postgresql.jdbc.TimestampUtils.toTimestamp(TimestampUtils.java:386) ~[postgresql-42.2.2.jar:42.2.2]
    at org.postgresql.jdbc.PgResultSet.getTimestamp(PgResultSet.java:610) ~[postgresql-42.2.2.jar:42.2.2]
    at org.postgresql.jdbc.PgResultSet.getTimestamp(PgResultSet.java:2513) ~[postgresql-42.2.2.jar:42.2.2]

I ran this problem down to the following line, where Postgres finishes reading the date and only skips whitespaces, but not a 'T' character. So Postgres would happily accept a dateTime in the format 2018-04-02 09:05:30 without a 'T'. The problem is, marklogic doesnt. A missing 'T' in a dateTime sadly is not a valid dateTime in MarkLogic (check xs:dateTime('2018-04-02 09:05:30')).

This leads me to just one single solution, forking the Postgres Driver source and add code to skip that 'T' char.

Is there another solution i am missing?

Solution

  • Template value extractions support a limited number of XPath and transformation functions. See details on the template API here. When the template engine is indexing the views it can index transformations of the document values.

    For your use-case you could configure the template view to replace the T in the dateTime with a space. The snippet below just replaces the <tde:val>mycolumn</tde:val> element in the template with <tde:val>fn:replace(mycolumn, 'T', ' ')</tde:val>. Since PostgreSQL requires date times formatted differently the MarkLogic dateTime you can index the column as a string by setting <tde:scalar-type>string</tde:scalar-type>. The tde:node-data-extract call should return a row with the string formatted for a PostgreSQL dateTime without the T.

    xquery version "1.0-ml";

let $template := <tde:template xmlns:tde='http://marklogic.com/xdmp/tde'
              xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'
              xsi:schemaLocation='http://marklogic.com/xdmp/tde'>
  <tde:context>/mytable</tde:context>
  <tde:enabled>true</tde:enabled>
  <tde:rows>
    <tde:row>
      <tde:schema-name>myschema</tde:schema-name>
      <tde:view-name>myview</tde:view-name>
      <tde:columns>
        <tde:column>
          <tde:name>mycolumn</tde:name>
          <tde:scalar-type>string</tde:scalar-type>
          <tde:val>fn:replace(mycolumn, 'T', ' ')</tde:val>
        </tde:column>
      </tde:columns>
    </tde:row>
  </tde:rows>
</tde:template>

let $node-valid-date := <mytable><mycolumn>2018-04-02T09:05:30</mycolumn></mytable>

return tde:node-data-extract($node-valid-date, $template)