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):
idint
sttime not null
ettime 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.
idst 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)
idst 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