R: sql comparison between records

[Finucci Domenico <Domenico.Finucci@xxxxxxxxxxxx>]

May this help you ?  
Let's assume TS is a Timestamp field
and data file is 
TS                          DESC
2001-01-01-00.40.00.200000  a
2001-01-01-00.50.00.200000  b
2001-01-01-00.00.00.300000  c
********   Fine dati   ********
By using 
select   date(ts),
         avg(hour(time(ts)))     hh ,
         avg(minute(time(ts)))   mm,
         avg(second(time(ts)))   ss
 from temp/prova
group by date(ts)

you will get 
....+....1....+....2....+....3....+....4....+....5....+....
DATE ( TS )             HH           MM              SS
 01/01/01                0              30               0
********   Fine dati   ********
I hope this help.
Sincerely 
Domenico Finucci
Fiditalia , Milano, 02- 4301-2494





-----Messaggio originale-----
Da: Joel Fritz [mailto:JFritz@sharperimage.com]
Inviato: mercoledì 25 luglio 2001 17.14
A: 'MIDRANGE-L@midrange.com'
Oggetto: RE: sql comparison between records


I need to calculate the average duration between records for records that
can be grouped by key.  The real world application is mean time between
clicks in a web site session.  Regardless, it's kind of interesting in the
abstract, IMHO.  I was a big fan of Joe Celko's column when it used to
appear in a now defunct freebie trade magazine.  One of the recurring
implicit themes of his column was the difference between the types of
problems that were naturally suited to a procedural language and those that
were better suited to SQL.  

> -----Original Message-----
> From: thomas@inorbit.com [mailto:thomas@inorbit.com]
> Sent: Tuesday, July 24, 2001 8:01 PM
> To: MIDRANGE-L@midrange.com
> Subject: Re: sql comparison between records
> 
> 
> Joel:
> 
> Since SQL is designed to handle sets of records, individual 
> record operations often aren't appropriate for it. But can 
> you clarify whether you actually need specific durations 
> between records or you want to use those durations only to 
> end up with an eventual average and the individual durations 
> will then be discarded? The second alternative might be 
> reasonably possible with SQL.
> 
> Tom Liotta
> 
> On Fri, 20 July 2001, Joel Fritz wrote:
> 
> > 
> > How do you do this in SQL?
> > 
> > I have a file ordered by key value and a timestamp.  There 
> are multiple
> > records for each key value. I want to find the difference 
> between time
> > stamps of successive records and calculate the average 
> difference for a key
> > value group.  I know, it's easy in RPG.  I'm not trying to measure
> > programmer productivity. <g>
> 
> -- 
> Tom Liotta
> The PowerTech Group, Inc.
> 19426 68th Avenue South
> Kent, WA 98032
> Phone  253-872-7788
> Fax  253-872-7904
> http://www.400Security.com
> 
> 
> ___________________________________________________
> The ALL NEW CS2000 from CompuServe
>  Better!  Faster! More Powerful!
>  250 FREE hours! Sign-on Now!
>  http://www.compuserve.com/trycsrv/cs2000/webmail/
> 
> 
> 
> 
> +---
> | This is the Midrange System Mailing List!
> | To submit a new message, send your mail to MIDRANGE-L@midrange.com.
> | To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
> | To unsubscribe from this list send email to 
> MIDRANGE-L-UNSUB@midrange.com.
> | Questions should be directed to the list owner/operator: 
> david@midrange.com
> +---
> 
+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator:
david@midrange.com
+---
+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---