Re: Calculate timedifferences within one database column

Thank you for the response. Additionally, I would like to have just the lines 1,3,5,7,9, ...

How can I do this ?

Michel Cadot schrieb:

> "Burkhard Kiesel" <burkhard.kiesel_at_med.siemens.de> a écrit dans le message news:
> 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
> >
>
> v815> create table t (col date);
> v815> insert into t values (to_date('2001-07-27 08:37:00','YYYY-MM-DD HH24:MI:SS'));
> v815> insert into t values (to_date('2001-07-27 08:42:28','YYYY-MM-DD HH24:MI:SS'));
> v815> insert into t values (to_date('2001-07-27 08:43:38','YYYY-MM-DD HH24:MI:SS'));
> v815> insert into t values (to_date('2001-07-27 08:44:14','YYYY-MM-DD HH24:MI:SS'));
> v815> insert into t values (to_date('2001-07-27 08:47:31','YYYY-MM-DD HH24:MI:SS'));
> v815> commit;
> v815> select to_char(col, 'YYYY-MM-DD HH24:MI:SS') from t order by col;
>
> TO_CHAR(COL,'YYYY-M
> -------------------
> 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
>
> 5 rows selected.
>
> v815> select to_char(t1.col, 'YYYY-MM-DD HH24:MI:SS') "Begin",
> 2 to_char(t2.col, 'YYYY-MM-DD HH24:MI:SS') "End",
> 3 (t2.col-t1.col)*86400 "Duration (s)"
> 4 from t t2, t t1
> 5 where not exists (select 1 from t where t.col>t1.col and t.col<t2.col)
> 6 and t2.col > t1.col
> 7 union all
> 8 select to_char(max(col), 'YYYY-MM-DD HH24:MI:SS'),
> 9 to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') "End",
> 10 (sysdate-max(col))*86400 "Duration (s)"
> 11 from t
> 12 order by 1
> 13 /
>
> Begin End Duration (s)
> ------------------- ------------------- ------------
> 2001-07-27 08:37:00 2001-07-27 08:42:28 328
> 2001-07-27 08:42:28 2001-07-27 08:43:38 70
> 2001-07-27 08:43:38 2001-07-27 08:44:14 36
> 2001-07-27 08:44:14 2001-07-27 08:47:31 197
> 2001-07-27 08:47:31 2001-07-30 15:06:26 281935
>
> 5 rows selected.
>
> --
> Hope this helps
> Michel
Received on Tue Jul 31 2001 - 03:54:22 CDT