Search code examples
sqlitetimerows

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
Doe,Janie       2011-06-20 05:15:12

The results are ordered by ReviewDT and grouped by CustomerName, such as:

SELECT
  CustomerName,
  ReviewDT
FROM
  Reviews
WHERE
  CustomerName NOT NULL
ORDER BY CustomerName ASC, ReviewDT ASC;

I'd like to create a column of the time difference between each row of this query for each Customer... rowid gives the original row, and there is no pattern to the inclusion from the rowid etc...

For the 1st entry for a CustomerName, the value would be 0. I am asking here incase this is something that can be calculated as part of the original query somehow. If not, I was planning to do this by a series of queries - initially creating a new TABLE selecting the results of the query above - then ALTERING to add the new column and using UPDATE/strftime to get the time differences by using rowid-1 (somehow)...

Solution

  • 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
                       order by r.ReviewDT DESC limit 1), 
                       q.ReviewDT))
  FROM Reviews as q WHERE q.CustomerName NOT NULL 
  ORDER BY q.CustomerName ASC, q.ReviewDT ASC;

    The result of the above query looks like this sqliteonline.com screenshot

    sqlitenonline.com ReviewDT

    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
                      order by r.ReviewDT DESC limit 1), 
                      q.ReviewDT)
 FROM Reviews as q WHERE q.CustomerName NOT NULL 
 ORDER BY q.CustomerName ASC, q.ReviewDT ASC;

    The result for this fiddle sample looks like this

    ReviewDT 2