MarkLogic dateTime using SQL

Question

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?

Elijah Bernstein-Cooper · Accepted Answer · 2018-08-06T12:52:09.510

4

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)

edited Aug 06 '18 at 12:52

answered Aug 05 '18 at 20:26

Elijah Bernstein-Cooper

505
2
11

Just a minor missunderstanding: Postgresql needs the data to not have a 'T'. So my template would look like this: ` mycolumn string fn:replace(mycolumn, 'T', ' ') ... let $node-valid-date := 2018-04-02T09:05:30 return tde:node-data-extract(($node-valid-date), $template)` – Wagner Michael Aug 06 '18 at 07:00
This only works with a scalar-type of `string`. Surprisingly queries like `select day(mycolumn) from myview` do still work. Do you know of any drawbacks this could have for like indexing or maybe others? – Wagner Michael Aug 06 '18 at 07:00
Using a `string` scalar-type seems to work for me so far without any other problems. Thanks alot for your comprehensive answer, again! – Wagner Michael Aug 06 '18 at 09:11
Thanks for correcting my misunderstanding @maffelbaffel. I'll update the answer. – Elijah Bernstein-Cooper Aug 06 '18 at 12:38
`Surprisingly queries like select day(mycolumn) from myview do still work.` This is because `day(mycolumn)` is executed on MarkLogic side, before Postgresql touches it, and it returns a string which cannot be misunderstood by Postgresql.. – grtjn Aug 09 '18 at 14:52
Not sure about drawbacks of using string over dateTime, probably none if you only use sql and odbc to query the data.. – grtjn Aug 09 '18 at 14:53

MarkLogic dateTime using SQL

1 Answers1