-
1. Re: Extract difference in miliseconds between timestamps
shawkins May 20, 2014 7:54 AM (in response to gadeyne.bram)SQL_TSI_FRAC_SECOND is implemented. Do you mean that it's not implemented on a target source?
-
2. Re: Extract difference in miliseconds between timestamps
gadeyne.bram May 21, 2014 2:42 AM (in response to shawkins)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
-
3. Re: Extract difference in miliseconds between timestamps
shawkins May 21, 2014 8:47 AM (in response to gadeyne.bram)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?
-
4. Re: Extract difference in miliseconds between timestamps
shawkins May 22, 2014 9:35 AM (in response to 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.