5 Replies Latest reply: Apr 28, 2009 7:31 AM by 650227 RSS

    TimeStampDiff

    paragrk
      Hello experts,

      I want to know How to use TimeStampDiff function appropriately.

      My requirement is I have a column in the physical layer say time. It is of datatype varchar(5). It stores the values in HH:MM format.

      I want to have the difference between the current time and this column.

      For current time, Im using function in OBI Answers: CURRENT_TIME(2) where 2 denotes the precision of seconds.

      The formula Im using is TIMESTAMPDIFF(SQL_TSI_MINUTE, CAST("Query Time".Time AS TIMESTAMP), CURRENT_TIME(2)).

      This is throwing the following error:

      ORA-01843: not a valid month at OCI call OCIStmtExecute: select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3, D1.c4 as c4, D1.c5 as c5, max(D1.c6) over () as c6 from (select distinct D1.c2 as c1, D1.c3 as c2, D1.c4 as c3, TO_DATE('1900-01-01 03:54:07' , 'YYYY-MM-DD HH24:MI:SS') as c4, D1.c1 as c5, D1.c1 as c6 from (select max(T45654.TOTAL_TIME_SEC) as c1, TRUNC(T45677.DAY_DT) as c2, ROUND( ( TO_DATE('1900-01-01 03:54:07' , 'YYYY-MM-DD HH24:MI:SS') - CAST( cast(T45654.START_HOUR_MIN as DATE) as DATE) ) * 1440 ) as c3, T45654.START_HOUR_MIN as c4 from S_ETL_DAY T45677, S_NQ_ACCT T45654 where ( T45654.START_DT = T45677.DAY_DT and T45654.SAW_SRC_PATH = '/shared/PI BOOK of Business - P2/AE Home Page/Search/P2 - Trust Request' and TRUNC(T45654.START_DT) = TO_DATE('2009-03-09' , 'YYYY-MM-DD') and TRUNC(T45677.DAY_DT) = TO_DATE('2009-03-09' , 'YYYY-MM-DD') ) group by T45654.START_HOUR_MIN, TRUNC(T45677.DAY_DT) ) D1 ) D1 order by c1 desc, c2, c3, c4. [nQSError: 17011] SQL statement execution failed. (HY000).

      Can anybody help me get the right formula?

      thx,
      parag
        • 1. Re: TimeStampDiff
          650227
          Hi.

          I try the similar:

          TIMESTAMPDIFF(SQL_TSI_MINUTE, CAST('14:22' AS TIMESTAMP), CURRENT_TIME(2))

          and I got an error:

          State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 17001] Oracle Error code: 1843, message: ORA-01843: not a valid month at OCI call OCIStmtExecute: select distinct D1.c1 as c1, ROUND( ( TO_DATE('1900-01-01 12:05:33' , 'YYYY-MM-DD HH24:MI:SS') - CAST( cast('14:23' as DATE) as DATE) ) * 1440 ) as c2 from (select distinct T21473.PROD_CATEGORY as c1 from PRODUCTS T21473 ) D1 order by c1, c2. [nQSError: 17011] SQL statement execution failed. (HY000)

          So the syntax is:

          TimestampDiff (interval, timestamp-expression1, timestamp-expression2)

          If you harcode it:

          Timestampdiff(SQL_TSI_MINUTE, TIMESTAMP '2009-04-28 12:00:00', current_timestamp)

          The results is difference in minutes.

          So you just need to put 'the right expression' in the second place. Try to concatenate current date with your time value and place it insted of TIMESTAMP '2009-04-28 12:00:00' because this inputs only timestamp format.

          Hope this helps.

          Regards,
          Goran
          http://108obiee.blogspot.com/
          • 2. Re: TimeStampDiff
            paragrk
            Hello Goran,

            How do i convert my Time column(HH:MM) to a timestamp datatype so as to use it in the function?

            thx,
            parag
            • 3. Re: TimeStampDiff
              650227
              Hi.

              This will work:

              Timestampdiff
              (
              SQL_TSI_MINUTE,
              EVALUATE('TO_TIMESTAMP(TO_CHAR(%1, %2)||%3, %4)' as TIMESTAMP, CURRENT_DATE, 'yyyy-mm-dd', *'12:00:00'*, 'yyyy-mm-dd hh24:mi:ss'),
              CURRENT_TIMESTAMP
              )

              Basically, you are using Oracle TO_TIMESTAMP and TO_CHAR functions with EVALUATE.

              Instead of *'12:00:00'* you put time column that you have defined.

              Regards,
              Goran
              http://108obiee.blogspot.com
              • 4. Re: TimeStampDiff
                paragrk
                Wow!
                Genius Goran, many many thanks.

                parag
                • 5. Re: TimeStampDiff
                  650227
                  Hi.

                  I'm glad that this works for you.

                  Regards,
                  Goran
                  http://108obiee.blogspot.com/