Reader's Comments

You state: >> no way in standard SQL to refer to "the value of this column from the previous row in the report".

At least in Oracle 8i SQL, there is a way in to refer to this, I'm sure it isn't standard, but useful nonetheless, and so I present it here.

It is called an Analytic Function. There are several, but the one demonstrated in this example is LAST_VALUE.

SELECT r1, r2, r2 - r1 reg_gap FROM (SELECT u1.update_date AS r1, LAST_VALUE (update_date) OVER (ORDER BY update_date ASC ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) AS r2 FROM users u1 WHERE u1.user_id > 100000) WHERE r1 <> r2 ORDER BY r1

From the inside out, I take the update_date from the users table, and using the LAST_VALUE function, I ask for the last update_date value, including in the window the current row and the next chronologically ordered row.

I used a higher level query to do the difference simply to avoid repeating the long function, but I could have done it in one.

The results are the same:

"R1" "R2" "REG_GAP" 11/10/2003 5:19:00 PM 11/10/2003 8:23:24 PM 0.128055555555556 11/10/2003 8:23:24 PM 11/12/2003 7:53:10 AM 1.47900462962963 11/12/2003 7:53:10 AM 2/13/2004 3:44:47 PM 93.3275115740741

Although, as I said, I'm using 8i so I don't have the interval type.

To find out more about Analytic Functions, check out the Oracle Documentation SQL Reference.

KSF

-- K SF, September 1, 2004

"Some Profoundly Painful Things -- Calculating time intervals between rows in a table" is very useful, thank you. Some people may need the following technique to establish a sequential numeric identifier. (In the example you assume "user_id column, which we know to be sequential and unique")

declare @tmp (registration_date datetime)

insert @tmp

select identity(int,1,1) as Sequence, registration_date into #x from users order by registration_date

... (now use #x instead of users in the example)

drop table #x

-- Steve Davis, January 29, 2006

You say: "Oops. Oracle pads some of these fields by default so that reports will be lined up and neat. We'll have to trim the strings ourselves." Not quite: one can use FM modifier in format string to instruct Oracle to trim whitespace from resulting string automatically, like this:

SQL> select to_char(sysdate,'Day, Month DD, YYYY') from dual;

TO_CHAR(SYSDATE,'DAY,MONTHDD,
-----------------------------
Monday   , May       22, 2006

SQL> select to_char(sysdate,'FMDay, Month DD, YYYY') from dual;

TO_CHAR(SYSDATE,'FMDAY,MONTHD
-----------------------------
Monday, May 22, 2006

Note that FM is a switch - second FM in format string negates the effect of the first.

-- Vladimir Zakharychev, May 22, 2006

Not pretty at all but it works...

CREATE OR REPLACE FUNCTION interval_to_seconds(x INTERVAL DAY TO SECOND ) RETURN NUMBER IS
s VARCHAR2(26);
days_s VARCHAR2(26);
time_s VARCHAR2(26);
N NUMBER(10,6);
BEGIN
  s := TO_CHAR(x);
  days_s := SUBSTR(s,2,INSTR(s,' ')-2);
  time_s := SUBSTR(s,2+LENGTH(days_s)+1);
  N := 86400*TO_NUMBER(days_s) + 3600*TO_NUMBER(SUBSTR(time_s,1,2)) + 60*TO_NUMBER(SUBSTR(time_s,4,2)) + TO_NUMBER(SUBSTR(time_s,7));
  IF SUBSTR(s,1,1) = '-' THEN
     N := - N; 
  END IF;
  RETURN N; 
END;

-- Andre Mostert, June 20, 2006

1.Find the first monday on every quater based on date ?

Select Next_day(trunc(to_date(sysdate,'DD-MON-YYYY'), 'Q')-1,'Monday') from dual

-- Mohamed Kaleel, April 13, 2007

Computing number of seconds in an interval:

FUNCTION seconds_from_interval(invInterval IN INTERVAL DAY TO SECOND) RETURN NUMBER IS BEGIN

RETURN EXTRACT (DAY FROM invInterval) * 86400 +

EXTRACT (HOUR FROM invInterval) * 3600 +

EXTRACT (MINUTE FROM invInterval) * 60 +

EXTRACT (SECOND FROM invInterval);

END seconds_from_interval;

-- Bob Jarvis, March 4, 2008