Extract difference in miliseconds between timestamps

[gadeyne.bram]

Hi,

I'm using Teiid 8.7

Is there a way to calculate the difference between 2 timesamps in miliseconds?

Apparently the SQL_TS_IFRAC_SECONDS are not yet implemented so I tried formatting the timestamp with simpleDateFormat S.

select FORMATTIMESTAMP(cl.endtime,'S') as betweens

from logs cl

This gets translated to the query below for postgresql

SELECT TO_CHAR(g_0."endtime", FF1) AS c_0 FROM "public"."chesttestlogs" AS g_0 LIMIT 100

This does not work since postgresql does not understand FF1.

I was going to make something like this: (PARSEINTEGER(FORMATTIMESTAMP (cl.endtime, 'S')) + PARSEINTEGER(FORMATTIMESTAMP (cl.endtime, 's')) * 60) - PARSEINTEGER(FORMATTIMESTAMP (cl.starttime, 'S')) + PARSEINTEGER(FORMATTIMESTAMP (cl.starttime, 's')) * 60)

Does anyone know an alternative way to solve this?

With kind regards

Bram

[shawkins]

SQL_TSI_FRAC_SECOND is implemented.  Do you mean that it's not implemented on a target source?

[gadeyne.bram]

Hi Steven,

I have this query: select TIMESTAMPDIFF(SQL_TSI_FRAC_SECOND,starttime, endtime) from chesttestlogs order by run desc, round desc;

But I get this exception:

08:38:40,625 WARN  [org.teiid.CONNECTOR] (Worker174_QueryProcessorQueue261526) bPgc6LhoM17z Connector worker process failed for atomic-request=bPgc6LhoM17z.13.0.44509: org.teiid.translator.jdbc.JDBCExecutionException: 0 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT {fn timestampdiff(SQL_TSI_FRAC_SECOND, g_0."starttime", g_0."endtime")} AS c_0 FROM "public"."chesttestlogs" AS g_0 ORDER BY g_0."run" DESC, g_0."round" DESC LIMIT 100]

        at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131)

        at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:325) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]

        at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:298) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]

        at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:110) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]

        at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:107) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]

        at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:334) [rt.jar:1.7.0_25]

        at java.util.concurrent.FutureTask.run(FutureTask.java:166) [rt.jar:1.7.0_25]

        at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:58) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]

        at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:274) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]

        at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]

        at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:214) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]

        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [rt.jar:1.7.0_25]

        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [rt.jar:1.7.0_25]

        at java.lang.Thread.run(Thread.java:724) [rt.jar:1.7.0_25]

Caused by: org.postgresql.util.PSQLException: Interval SQL_TSI_FRAC_SECOND not yet implemented

        at org.postgresql.jdbc2.EscapedFunctions.constantToDatePart(EscapedFunctions.java:564)

        at org.postgresql.jdbc2.EscapedFunctions.sqltimestampdiff(EscapedFunctions.java:534)

        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.7.0_25]

        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) [rt.jar:1.7.0_25]

        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.7.0_25]

        at java.lang.reflect.Method.invoke(Method.java:606) [rt.jar:1.7.0_25]

        at org.postgresql.jdbc2.AbstractJdbc2Statement.escapeFunction(AbstractJdbc2Statement.java:1019)

        at org.postgresql.jdbc2.AbstractJdbc2Statement.parseSql(AbstractJdbc2Statement.java:974)

        at org.postgresql.jdbc2.AbstractJdbc2Statement.replaceProcessing(AbstractJdbc2Statement.java:832)

        at org.postgresql.jdbc2.AbstractJdbc2Statement.<init>(AbstractJdbc2Statement.java:147)

        at org.postgresql.jdbc3.AbstractJdbc3Statement.<init>(AbstractJdbc3Statement.java:40)

        at org.postgresql.jdbc3g.AbstractJdbc3gStatement.<init>(AbstractJdbc3gStatement.java:26)

        at org.postgresql.jdbc4.AbstractJdbc4Statement.<init>(AbstractJdbc4Statement.java:30)

        at org.postgresql.jdbc4.Jdbc4Statement.<init>(Jdbc4Statement.java:28)

        at org.postgresql.jdbc4.Jdbc4PreparedStatement.<init>(Jdbc4PreparedStatement.java:21)

        at org.postgresql.jdbc4.Jdbc4PreparedStatement.<init>(Jdbc4PreparedStatement.java:16)

        at org.postgresql.jdbc4.Jdbc4Connection.prepareStatement(Jdbc4Connection.java:37)

        at org.postgresql.jdbc3.AbstractJdbc3Connection.prepareStatement(AbstractJdbc3Connection.java:273)

        at org.jboss.jca.adapters.jdbc.BaseWrapperManagedConnection.doPrepareStatement(BaseWrapperManagedConnection.java:732)

        at org.jboss.jca.adapters.jdbc.BaseWrapperManagedConnection.prepareStatement(BaseWrapperManagedConnection.java:718)

        at org.jboss.jca.adapters.jdbc.WrappedConnection.prepareStatement(WrappedConnection.java:404)

        at org.teiid.translator.jdbc.JDBCBaseExecution.getPreparedStatement(JDBCBaseExecution.java:184)

        at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:121)

        ... 13 more

[shawkins]

Yes, we need to compensate for that appropriately if we are pushing down.

With the formatting issue we are simply expecting Oracle syntax to work, which does have a difference in FF support.

Can you log an issue for these?

[shawkins]

I went ahead and logged https://issues.jboss.org/browse/TEIID-2969 as there seems to be quite a bit going on here.  It looks like most of what pg supports for timestampdiff does not match our expectations.