SQLite Forum

Time difference between two records
Login
Hello

Lets assume the following database

    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");
    INSERT INTO pos (pos, start) VALUES ("B", "2020-01-01 15:30:00");
    INSERT INTO pos (pos, start) VALUES ("C", "2020-01-01 16:00:00");

I would like to create a view with the following information:

POS|Start              |Duration (h)
A  |2020-01-01 12:00:00|3.5
B  |2020-01-01 15:30:00|1
C  |2020-01-01 16:00:00| ??

where ?? is the value of the last column and it should be calculated by: end_of_year - datetime_of_last_dataset_of_the_year

Ist this possible in one view?