You could join the table against itself, matching each row against every following record and then summarizing to find the "next" one. If your table is of a fair size, you'd need to play with the optimizer to make sure you get the efficiency you need. The SQL would be along the lines of:
select max(curRow.id), time_to_sec(min(matchedRow.st)) - time_to_sec(max(curRow.et)) from events as curRow, events as matchedRow where curRow.et <= matchedRow.st group by curRow.id On 21/5/02 at 10:43 am, David Shields <[EMAIL PROTECTED]> wrote: > Probably a very simple one for you guys, but I'm stuck. > Given a simple table (events): > id int > st time not null > et time not null > > I know I can get duration by converting st and et to secs and subtracting > (that bits easy), but how would I go about getting the gap between > successive records IN MY SQL STATEMENT (without processing the result set > in my application language (php)): i.e. > id st et > 1 09:00 10:00 > 2 10:15 10:45 > 3 11:45 12:00 > .... > i.e. my result wants to be (assuming data is good, and sort by st) > > id st et gap_since_last_row > 1 09:00 10:00 0 (no prev row) > 2 10:15 10:45 0:15 > 3 11:45 12:00 1:00 > .... > I suspect I should be using SQL variables, but docs seem a bit opaque. > > Anyone point me in right direction ? > > David. > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
