Datetime progressive sum PostgreSQL

Question

my first question here, but I looked in many other places and could not find the answer to my problem.

 id | track_seg_id | time 
----+--------------+---------------------
 1 | 0 | 2020年05月23日 07:45:32
 2 | 0 | 2020年05月23日 07:46:04
 3 | 0 | 2020年05月23日 07:46:53
 4 | 0 | 2020年05月23日 07:46:58
 5 | 0 | 2020年05月23日 07:47:03
 6 | 0 | 2020年05月23日 07:47:08
 7 | 0 | 2020年05月23日 07:47:11
 8 | 0 | 2020年05月23日 07:47:16
 9 | 0 | 2020年05月23日 07:47:20
 10 | 0 | 2020年05月23日 07:47:23

What I want to do is to get a progressive sum of seconds from the first to the next row.

 id | track_seg_id | time | prog 
----+--------------+---------------------+---------------------
 1 | 0 | 2020年05月23日 07:45:32 | 0
 2 | 0 | 2020年05月23日 07:46:04 | 32
 3 | 0 | 2020年05月23日 07:46:53 | 81
 4 | 0 | 2020年05月23日 07:46:58 | 86
 5 | 0 | 2020年05月23日 07:47:03 | 91
 6 | 0 | 2020年05月23日 07:47:08 | 96
 7 | 0 | 2020年05月23日 07:47:11 | 99
 8 | 0 | 2020年05月23日 07:47:16 | 104
 9 | 0 | 2020年05月23日 07:47:20 | 108
 10 | 0 | 2020年05月23日 07:47:23 | 111

I hit different resources, calculate cumulative sum running total and calculating cumulative sum , but I wasn't able to solve this problem.

Answer 1

I think you mean "seconds", not "minutes", and your values are slightly off.

If my suspicion is right, you could do it with the window function first_value and use extract to extract the number of seconds:

SELECT *,
 EXTRACT(epoch FROM
 time - first_value(time) OVER (ORDER BY time)
 )
FROM mytable;
 id | track_seg_id | time | date_part 
----+--------------+---------------------+-----------
 1 | 0 | 2020年05月23日 07:45:32 | 0
 2 | 0 | 2020年05月23日 07:46:04 | 32
 3 | 0 | 2020年05月23日 07:46:53 | 81
 4 | 0 | 2020年05月23日 07:46:58 | 86
 5 | 0 | 2020年05月23日 07:47:03 | 91
 6 | 0 | 2020年05月23日 07:47:08 | 96
 7 | 0 | 2020年05月23日 07:47:11 | 99
 8 | 0 | 2020年05月23日 07:47:16 | 104
 9 | 0 | 2020年05月23日 07:47:20 | 108
 10 | 0 | 2020年05月23日 07:47:23 | 111
(10 rows)

• matteo: thanks @Laurenz Albe (I also corrected the question). Your solution is the same of @Lennard. Is there any difference in using the EXTRACT function once or twice?
• Laurenz Albe: My solution is slightly simpler and cheaper, but that's maybe not even measurable.
• Lennart - Slava Ukraini: @matteo I choose my solution due to inexperience with time handling in PostgreSQL. However, I noticed that my solution seems to introduce an error for fractions of seconds, where as Laurenz does not. See: dbfiddle.uk/…
• Vérace: @Lennart - any idea what's causing this error?
• Laurenz Albe: @Vérace Probably rounding errors - EXTRACT returns double precision.

Answer 2

One way to do it:

select extract(EPOCH from ts) 
 - extract(EPOCH from first_value(ts) over (order by ts))
from t;

• matteo: thanks @Lennart for the answer. It was very helpful