Problem solve Get help with specific problems with your technologies, process and projects.

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 last published in August 2002

Dig Deeper on Oracle and SQL

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.

Start the conversation

Send me notifications when other members comment.

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

Please create a username to comment.