5

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.

6

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)
6
  • 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?
    – matteo
    Jun 17 '20 at 13:47
  • 1
    My solution is slightly simpler and cheaper, but that's maybe not even measurable. Jun 17 '20 at 13:53
  • @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/…
    – Lennart
    Jun 17 '20 at 14:03
  • @Lennart - any idea what's causing this error?
    – Vérace
    Jun 17 '20 at 20:20
  • @Vérace Probably rounding errors - EXTRACT returns double precision. Jun 18 '20 at 6:56
4

One way to do it:

select extract(EPOCH from ts) 
     - extract(EPOCH from first_value(ts) over (order by ts))
from t;
1
  • thanks @Lennart for the answer. It was very helpful
    – matteo
    Jun 17 '20 at 14:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.