Datetime progressive sum PostgreSQL
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)
One way to do it:
select extract(EPOCH from ts)
- extract(EPOCH from first_value(ts) over (order by ts))
from t;