Time Difference Between Query Result Rows In Sqlite: How To?

Consider the following reviews table contents: CustomerName ReviewDT Doe,John 2011-06-20 10:13:24 Doe,John 2011-06-20 10:54:45 Doe,John 2011-06-20 11:36:34

Solution 1:

To compute the seconds elapsed from one ReviewDT row to the next:

SELECT q.CustomerName, q.ReviewDT,
   strftime('%s',q.ReviewDT) 
   - strftime('%s',coalesce((select r.ReviewDT from Reviews as r
                       where r.CustomerName = q.CustomerName
                       and r.ReviewDT < q.ReviewDT
                       orderby r.ReviewDT DESC limit 1), 
                       q.ReviewDT))
  FROM Reviews as q WHERE q.CustomerName NOTNULLORDERBY q.CustomerName ASC, q.ReviewDT ASC;

To get the DT of each ReviewDT and its preceding CustomerName row:

SELECT q.CustomerName, q.ReviewDT,
  coalesce((select r.ReviewDT from Reviews as r
                      where r.CustomerName = q.CustomerName
                      and r.ReviewDT < q.ReviewDT
                      orderby r.ReviewDT DESC limit 1), 
                      q.ReviewDT)
 FROM Reviews as q WHERE q.CustomerName NOTNULLORDERBY q.CustomerName ASC, q.ReviewDT ASC;