Ask the Expert

Creating record set of time intervals by subtracting timestamp from the previous one

I have a table containing a timestamp. How can I generate a recordset containing the time interval by subtracting the timestamp column of the current record with the previous, assuming I've put the set in order of date?

    Requires Free Membership to View

If you were solving this problem procedurally, you could store the last date in a variable as you iterated through the ordered rows, however, SQL requires a different approach. For every row, simply take the difference between the date and the date which precedes it. I should note that this happens to be MS SQL Server flavored SQL.

create table Things
( ID numeric,
  SomeDate datetime );

insert into Things values (1,'01-JAN-2002 12:34PM');
insert into Things values (2,'01-JAN-2002 1:10PM');
insert into Things values (3,'01-JAN-2002 2:59PM');
insert into Things values (4,'01-JAN-2002 3:17PM');
insert into Things values (5,'01-JAN-2002 3:20PM');
insert into Things values (6,'01-JAN-2002 6:39PM');
insert into Things values (7,'01-JAN-2002 6:40PM');

select ID, SomeDate,
       cast( SomeDate ? ( select max(SomeDate) from Things 
                            where SomeDate < t.SomeDate ) 
             as float
           ) * 24 * 60 as MinutesSinceLastDate  
  from Things t;

ID SomeDate                MinutesSinceLastDate  
-- ----------------------- --------------------
1  2002-01-01 12:34:00.000 NULL
2  2002-01-01 13:10:00.000 36.000000000000007
3  2002-01-01 14:59:00.000 108.99999999999999
4  2002-01-01 15:17:00.000 18.000000000000004
5  2002-01-01 15:20:00.000 3.0
6  2002-01-01 18:39:00.000 199.0
7  2002-01-01 18:40:00.000 1.0

For More Information

This was first published in August 2002

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: