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
- Dozens more answers to tough SQL questions from Jason Law are available here.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
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.