4 Replies Latest reply on May 22, 2014 9:35 AM by Steven Hawkins

    Extract difference in miliseconds between timestamps

    gadeyne.bram Master

      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

        • 1. Re: Extract difference in miliseconds between timestamps
          Steven Hawkins Master

          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 Master

            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
              Steven Hawkins Master

              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
                Steven Hawkins Master

                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.