Difference between dates in database rows

[JTolhurst]

Hi,
I have been playing around with B4Android and a SQLite database and was wondering how I would go about creating a query which would return the difference between dates of adjacent table rows?

So in the example below I would want to be able to calculate the number of days since the previous order for each order date (so the DaysSince column).

Table: Result

Date | Test | DaysSince
20120819 | 4 | 1
20120818 | 7 | 3
20120815 | 4 | 5
20120810 | 6 | NULL

I presume I need to use the julianday function and then some special SQL?

Thank You

 

[Erel]

A simpler approach will be to go over the query result (make sure that it is sorted by date) and calculate the difference in Basic4android code.

 

[JTolhurst]

Ok, I figured it out for myself, the SQL statement required would be:

"SELECT q.Test, q.Date, strftime('%J',q.Date)  - strftime('%J',coalesce((select r.Date from Result as r WHERE r.Date < q.Date ORDER BY r.Date DESC limit 1), q.Date)) as DaysSince FROM Result as q WHERE q.Test NOT NULL ORDER BY q.Test ASC, q.Date ASC;"

The above SQL gave me what I needed, but for educational purposes I would be interested in being given a few pointers of how to calculate the difference using Basic4android code.

 

[Roger Garstang]

Calculations should work fine in B4A assuming you convert the ticks since one is seconds and the other is milliseconds.