Re: Calculate timedifferences within one database column

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 1 Aug 2001 09:19:51 +0100
Message-ID: <996653824.17319.0.nnrp-01.9e984b29@news.demon.co.uk>

Your requirement to make the order of
rows, and data changes between rows,
is somewhat contrary to 'proper' usage
of a relational database.

However, you could use analytic functions in 8.1.6+ Something like:

select

        rk,
        nvl(end_dt,sysdate) - dt

from (
select

    dt,
    rank() over(order by dt) as rk,
    lead(dt,1) over (order by dt) as end_dt from

    base_table
)
where mod(rk,2) = 1
order by rk
;

--
Jonathan Lewis

Host to The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases
See http://www.jlcomp.demon.co.uk/book_rev.html

Seminars on getting the best out of Oracle
See http://www.jlcomp.demon.co.uk/seminar.html

Screensaver or Lifesaver: http://www.ud.com
Use spare CPU to assist in cancer research.




Burkhard Kiesel wrote in message <3B6555A9.1EA44B6C_at_med.siemens.de>...


>Hi,

>

>

>I have a table with a datetime column, with the following data, e.g.:

>

>

>2001-07-27 08:37:00

>2001-07-27 08:42:28

>2001-07-27 08:43:38

>2001-07-27 08:44:14

>2001-07-27 08:47:31

>

>What I need is retrieving the information with a sql query which give

>the timedifference for every pair.

>If there is a last line (no corresponding end-time), the calculation

>should be SYSDATE - 2001-07-27 08:47:31

>E.g.:  2001-07-27 08:42:28 - 2001-07-27 08:37:00

>         2001-07-27 08:44:14 - 2001-07-27 08:43:38

>         SYSDATE - 2001-07-27 08:47:31

>

>How can I solve this ?

>

>Regards

>

>

>Burkhard Kiesel

>

Received on Wed Aug 01 2001 - 03:19:51 CDT