Q

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?

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

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close