Re: Calculate timedifferences within one database column

From: Michel Cadot <micadot_at_netcourrier.com>
Date: Mon, 30 Jul 2001 15:06:32 +0200
Message-ID: <9k3m4p$fbl$1@s1.read.news.oleane.net>

"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 Mon Jul 30 2001 - 08:06:32 CDT