X

Oracle Spatial and Graph – technical tips, best practices, and news from the product team

Better support for Timestamp with Zone in the latest Oracle Spatial and Graph Property Graph Patch

Alan Wu
Architect

Good news, we just released a patch for the Oracle Spatial and Graph Property Graph feature. It contains, among many things, PGX 3.1 and latest Data Access Layer. One important fix is a much better support of timestamp with time zone. 

Here is a quick example that illustrates the improvement.

cfg = GraphConfigBuilder.forPropertyGraphRdbms().setJdbcUrl("jdbc:oracle:thin:@127.0.0.1:1521:orcl122") .setUsername("pg").setPassword("<YOUR_PASSWORD>")  .setName("test_graph") .setMaxNumConnections(2) .setLoadEdgeLabel(false).addVertexProperty("ts", PropertyType.TIMESTAMP_WITH_TIMEZONE, null)  .addVertexProperty("name", PropertyType.STRING, "default_name")  .addEdgeProperty("cost", PropertyType.DOUBLE, "1000000")  .build();


opg = OraclePropertyGraph.getInstance(cfg);

v=opg.addVertex(10000l);
v.setProperty("ts", new Date(1000l));

opg.commit()

v=opg.getVertex(10000l);
==>Vertex ID 10000 [vertex] {ts:dat:1969-12-31 16:00:01.0}
 

Note that the above getVertex returns a property of type java.util.Date of which java.sql.Timestamp is a subclass. Now, let's read this graph into a remote PGX endpoint.

pgxSession=Pgx.getInstance("http://127.0.0.1:7007").createSession("session1");
analyst=pgxSession.createAnalyst();
pgxGraph = pgxSession.readGraphWithProperties(opg.getConfig(), true);

For some older versions of OSG PG, you are likely going to hit the following exception:
  "java.lang.UnsupportedOperationException: loading type time_with_timezone through DAL not yet supported"

With this new patch, the above will go through without a problem. To sanity check, run a simple PGQL query and print the type of the timestamp property ts.

pgxResultSet = pgxGraph.queryPgql("SELECT n.ts MATCH (n)")
elem=pgxResultSet.getResults()
e1=elem.iterator().next()
twz=e1.getTimestampWithTimezone(0)
==>1969-12-31T16:00:01Z

twz.getClass().getName()
==>java.time.OffsetDateTime
 

Cheers,

Zhe

References:

Oracle Spatial and Graph Property Graph Patch (28577866): https://support.oracle.com/epmos/faces/PatchDetail?patchId=28577866 

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.