7 Replies Latest reply: Feb 19, 2013 5:51 AM by 565797 RSS

    difference in milliseconds between starttimestamp and endtimestamp

    565797
      I'm having this issue....

      I have 2 columns starttimestamp and endtimestamp and both have 13:45:57 on their time...
      I would like to know the milliseconds of difference between them..How can achieve that....

      select (ENDTIMESTAMP-STARTTIMESTAMP) as tempo_execucao
      from eil_adapter_log_view
      WHERE TO_DATE ('13-02-2013 13:30:00', 'dd-mm-yyyy hh24:mi:ss') < starttimestamp
      AND TO_DATE ('11-02-2013 14:30:00', 'dd-mm-yyyy hh24:mi:ss') > starttimestamp
      and interfacename = 'Inbound/GetPortfolio.process'
      order by starttimestamp desc;

      Can anyone help?

      Best Regards,
      Rui Oliveira
        • 1. Re: difference in milliseconds between starttimestamp and endtimestamp
          sb92075
          user562794 wrote:
          I'm having this issue....

          I have 2 columns starttimestamp and endtimestamp and both have 13:45:57 on their time...
          I would like to know the milliseconds of difference between them..How can achieve that....

          select (ENDTIMESTAMP-STARTTIMESTAMP) as tempo_execucao
          from eil_adapter_log_view
          WHERE TO_DATE ('13-02-2013 13:30:00', 'dd-mm-yyyy hh24:mi:ss') < starttimestamp
          AND TO_DATE ('11-02-2013 14:30:00', 'dd-mm-yyyy hh24:mi:ss') > starttimestamp
          and interfacename = 'Inbound/GetPortfolio.process'
          order by starttimestamp desc;

          Can anyone help?

          Best Regards,
          Rui Oliveira
          select TO_CHAR(ENDTIMESTAMP-STARTTIMESTAMP), 'YYYY-MM-DD HH24:MI:SSxFF') as tempo_execucao
          • 2. Re: difference in milliseconds between starttimestamp and endtimestamp
            JustinCave
            What are the data types of the StartTimeStamp and the EndTimeStamp columns? Are they TIMESTAMP columns? DATE columns? VARCHAR2 columns?

            Justin
            • 3. Re: difference in milliseconds between starttimestamp and endtimestamp
              EdStevens
              user562794 wrote:
              I'm having this issue....

              I have 2 columns starttimestamp and endtimestamp and both have 13:45:57 on their time...
              I would like to know the milliseconds of difference between them..How can achieve that....

              select (ENDTIMESTAMP-STARTTIMESTAMP) as tempo_execucao
              from eil_adapter_log_view
              WHERE TO_DATE ('13-02-2013 13:30:00', 'dd-mm-yyyy hh24:mi:ss') < starttimestamp
              AND TO_DATE ('11-02-2013 14:30:00', 'dd-mm-yyyy hh24:mi:ss') > starttimestamp
              and interfacename = 'Inbound/GetPortfolio.process'
              order by starttimestamp desc;

              Can anyone help?

              Best Regards,
              Rui Oliveira
              What is the data type of EIL_ADAPTER_LOG_VIEW?
              • 4. Re: difference in milliseconds between starttimestamp and endtimestamp
                565797
                Justin Cave wrote:
                What are the data types of the StartTimeStamp and the EndTimeStamp columns? Are they TIMESTAMP columns? DATE columns? VARCHAR2 columns?

                Justin
                They are of type DATE...so the type TO_CHAR won't work i think...

                This was the error it gave....ORA-01481 invalid number format model
                • 5. Re: difference in milliseconds between starttimestamp and endtimestamp
                  sb92075
                  user562794 wrote:
                  Justin Cave wrote:
                  What are the data types of the StartTimeStamp and the EndTimeStamp columns? Are they TIMESTAMP columns? DATE columns? VARCHAR2 columns?

                  Justin
                  They are of type DATE..
                  DATE datatype do not contain any fractions of a second; only whole/integer seconds, so no millisecond difference is possible.
                  • 6. Re: difference in milliseconds between starttimestamp and endtimestamp
                    rp0428
                    >
                    I would like to know the milliseconds of difference between them
                    . . .
                    They are of type DATE...so the type TO_CHAR won't work i think...
                    >
                    You can't get millseconds from a value that doesn't have them.

                    See the Oracle Built-in Datatypes section of the SQL Language doc
                    http://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements001.htm#i54330
                    >
                    12
                    DATE
                    Valid date range from January 1, 4712 BC, to December 31, 9999 AD. The default format is determined explicitly by the NLS_DATE_FORMAT parameter or implicitly by the NLS_TERRITORY parameter. The size is fixed at 7 bytes. This datatype contains the datetime fields YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND. It does not have fractional seconds or a time zone.
                    >
                    Did you see the last sentence? 'It does not have fractional seconds or a time zone.'
                    • 7. Re: difference in milliseconds between starttimestamp and endtimestamp
                      565797
                      That makes it clear thank you very much for you're quick answers...

                      Best Regards,
                      Rui