I am new in SQL lite, I have a datetime column in this format:
| id | datetime |
|---|---|
| 1 | 2020-12-26 19:08:49 |
| 2 | 2020-12-26 19:08:50 |
| 3 | 2020-12-26 19:08:51 |
| 4 | 2020-12-26 19:08:51 |
| 5 | 2020-12-26 19:09:07 |
| 6 | 2020-12-26 19:11:45 |
| 7 | 2020-12-26 19:52:49 |
| 8 | 2020-12-26 19:52:50 |
How can i compute the difference between the first element with the 2nd the 3rd with 4th element ??
Use window function FIRST_VALUE() to get the datetime of the the 1st row and with the function strftime() you can calculate the difference of each row:
SELECT *
FROM (
SELECT id,
strftime('%s', datetime) - strftime('%s', FIRST_VALUE(datetime) OVER (ORDER BY id)) AS diff_in_secs
FROM tablename
)
WHERE id > 1
Or with a self join:
SELECT t.id,
strftime('%s', t.datetime) - strftime('%s', m.datetime)
FROM tablename t
INNER JOIN (SELECT * FROM tablename ORDER BY id LIMIT 1) m
ON m.id < t.id
I used id and datetime for the column names and tablename for the table's name, you can change them to the actual ones.
See the demo.
Results:
> id | diff_in_secs
> -: | -----------:
> 2 | 1
> 3 | 2
> 4 | 2
> 5 | 18
> 6 | 176
> 7 | 2640
> 8 | 2641