Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: time delta question...

Re: time delta question...

From: Herman de Boer <h.de.boer_at_itcg.nl>
Date: Fri, 19 Jul 2002 13:15:20 GMT
Message-ID: <ah93i2$8i0$1@news1.xs4all.nl>


Hello T.,

with the analytical functions, first time available in Enterprise edition 8.1.6, part of standard edition in 9.0 and above, you can write, if the table is called t1 and the date column d:

select (next_d - d) * (24 * 60) diff_in_minutes from ( select d

         ,      lead(d, 1) over (order by d)   next_d 
         from   t1
       ) 

Kind Regards,

Herman de Boer
sr consultant
IT Consultancy Group bv.

Telemachus wrote:

>Assume I have a list of datetimes e.g.
>
> Jan 12 17.03
>Jan 12 17.21
>Jan 12 18.02
>Jan 12 19.12
>Jan 13 03.24
>
>In SQL alone , is there a method of generating a list of time deltas ?
>
>eg for the list above to return
>(cast to minutes )
>
>18
>41
>70
>492
>
>One can see how it might be done through an iterative approach in code, but
>I'm wondering how to do it in plain 1-statement SQL.
>(off looking for my dog-eared copy of SQL for Smarties )
>
>T.
>
>
>
Received on Fri Jul 19 2002 - 08:15:20 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US