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

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

    Login

    By submitting your registration information to SearchOracle.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchOracle.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top