4 Replies Latest reply on Jun 26, 2015 12:38 PM by Ebin

    Need to avoid trunc in query when using timestampdiff

    2845107

      Hi,

       

      I am using

      timestampdiff(sql_tsi_day,"export_date",current_date) for date difference. Its coming fine

      But I am getting trunc in the physical query

      TRUNC( T43296.export_date) - TRUNC( TO_DATE('2015-06-25' , 'YYYY-MM-DD')

       

      I need to avoid trunc because its not giving decimal values as its giving when I do a date difference in Oracle.

       

      I think its because I am using 'SQL_TSI_DAY' if I try to use other intervals 'Minute' getting an error as below

       

      Formula syntax is invalid.

      [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 22025] Function TimestampDiff is called with an incompatible type. (HY000)

       

      Please suggest any workaround for the below oracle code

       

      ROUND (export_date - SYSDATE, 1) in obiee if I am missing anything in my approach

       

      Thanks.

        • 1. Re: Need to avoid trunc in query when using timestampdiff
          Ebin

          Hi,

           

          The below mentioned code is a workaround which gives decimal values for date difference.

           

          cast(TIMESTAMPDIFF(SQL_TSI_HOUR,T43296.export_date,NOW()) AS FLOAT)/24

           

          Thanks

          Ebin

          • 2. Re: Need to avoid trunc in query when using timestampdiff
            2845107

            Hi,

             

            Used to the above code its giving the error

             

            Formula syntax is invalid.

            [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 22025] Function TimestampDiff is called with an incompatible type. (HY000)

            • 3. Re: Need to avoid trunc in query when using timestampdiff
              dieluigu

              You're using "sql_tsi_day" and that is why the query sent to the database has a TRUNC, to get number of days. In order to get minutes precission you need to use SQL_TSI_MINUTE.

               

              A good example can be the following (displays the difference between two time stamp date columns in the format HH:MM:SS):

              Cast(TimeStampDiff(SQL_TSI_MINUTE,"Timesheets Measures"."Actual Arrival","Timesheets Measures"."Actual Departure")/(24*60) as VARCHAR(10)) ||'d '|| Cast(Mod(TimeStampDiff(SQL_TSI_MINUTE,"Timesheets Measures"."Actual Arrival","Timesheets Measures"."Actual Departure")/(60), 60) as VARCHAR(10)) ||'h '|| Cast(Mod(TimeStampDiff(SQL_TSI_MINUTE,"Timesheets Measures"."Actual Arrival","Timesheets Measures"."Actual Departure"), 60) as VARCHAR(10))||'m'

              • 4. Re: Need to avoid trunc in query when using timestampdiff
                Ebin

                TIMESTAMPDIFF function can be called only with 2 time stamp columns. Please let me know the answer to following questions :

                 

                1) Does the column T43296.export_date store value in datetime format ?

                2) What is the type of the column T43296.export_date ? In order for the timestampdiff function to work, this should be of DATETIME.

                3) The requirement is to identify the date with decimal precision between 2 timestamp columns ? Is that correct ?