In addition to what Gunther said, you can also use [Window functions (with a sufficiently new version of SQLite)](https://sqlite.org/windowfunctions.html#introduction_to_window_functions)
Example:
```
-- SQLite version 3.30.1 [ Release: 2019-10-10 ] on SQLitespeed version 2.1.3.11.
================================================================================================
CREATE table pos (
pos NVARCHAR(1),
start DATETIME,
CONSTRAINT "pk_pos" PRIMARY KEY ("pos")
);
INSERT INTO pos (pos, start) VALUES
("A", "2020-01-01 12:00:00")
,("B", "2020-01-01 15:30:00")
,("C", "2020-01-01 16:00:00")
;
SELECT *
FROM pos
;
-- pos |start
-- -----|---------------------
-- A |2020-01-01 12:00:00
-- B |2020-01-01 15:30:00
-- C |2020-01-01 16:00:00
WITH DSec(pos, start, baseSeconds, baseNextSeconds, endOfYearSeconds) AS (
SELECT pos, start, strftime('%s',start),
MAX(strftime('%s',start)) OVER (ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING),
strftime('%s','now','start of year','+1 year')
FROM pos
)
SELECT pos AS POS, start AS Start,
COALESCE(
(baseNextSeconds - baseSeconds)/3600.0,
(endOfYearSeconds - baseSeconds)/3600.0
) AS 'Duration (h)'
FROM DSec
;
-- POS |Start |Duration (h)
-- -----|---------------------|------------
-- A |2020-01-01 12:00:00 | 3.5
-- B |2020-01-01 15:30:00 | 0.5
-- C |2020-01-01 16:00:00 | 8768.0
```
Note that the CTE (WITH clause) is not needed and can easily be replaced by a sub-query, but I like it for the sake of clarity.
Feel free to ask if anything is unclear.
Good luck!
Ryan