postgres update table with time evolution column

Admin 1
Viewed 51

Having this table at hand:

SELECT * FROM mutable LIMIT 10;
user_id |     session_id     |       timestamp        
---------+--------------------+------------------------
     180 | 179020080820120904 | 2008-08-20 12:09:04+01
     180 | 179020080820120904 | 2008-08-20 12:09:07+01
     180 | 179020080820120904 | 2008-08-20 12:10:35+01
     180 | 179020080820120904 | 2008-08-20 12:10:37+01
     180 | 179020080820120904 | 2008-08-20 12:10:39+01
     180 | 179020080820120904 | 2008-08-20 12:10:41+01
     180 | 179020080820120904 | 2008-08-20 12:10:43+01
     180 | 179020080820120904 | 2008-08-20 12:10:45+01
     180 | 179020080820120904 | 2008-08-20 12:10:47+01
     180 | 179020080820120904 | 2008-08-20 12:10:49+01
(10 rows)

So I want to modify the table by adding a column t_evolution that shows keeping track of the duration from one record occurrence to the next, considering the timestamp columns, like so:

+---------------------------------------------------------+--------------------+
|     user_id |     session_id     |       timestamp      |    t_evolution     |
+---------------------------------------------------------+--------------------+
|      180 | 179020080820120904 | 2008-08-20 12:09:04+01  |        0           |
|      180 | 179020080820120904 | 2008-08-20 12:09:07+01  |        3           |
|      180 | 179020080820120904 | 2008-08-20 12:10:35+01  |       92           |
|      180 | 179020080820120904 | 2008-08-20 12:10:37+01  |       94           |
|      180 | 179020080820120904 | 2008-08-20 12:10:39+01  |       96           |
|      180 | 179020080820120904 | 2008-08-20 12:10:41+01  |       98           |
|      180 | 179020080820120904 | 2008-08-20 12:10:43+01  |      100           |
|      180 | 179020080820120904 | 2008-08-20 12:10:45+01  |      102           |
|      180 | 179020080820120904 | 2008-08-20 12:10:47+01  |      104           |
|      180 | 179020080820120904 | 2008-08-20 12:10:49+01  |      106           |
+---------------------------------------------------------+--------------------+
(10 rows)
aggregate-functions postgresql sql
1 Answers

You can subtract the 1st timestamp from each of the timestamps and use EXTRACT() to get the number of seconds.
With window function FIRST_VALUE():

SELECT *,
  EXTRACT(EPOCH FROM ("timestamp" - FIRST_VALUE("timestamp") OVER (ORDER BY "timestamp"))) t_evolution
FROM mutable

In your sample data all rows contain the same value for the columns user_id and session_id. If you want the new column to keeping track of the duration for each user_id and/or session_id then you may change the OVER clause to:

OVER (PARTITION BY user_id ORDER BY "timestamp")

or:

OVER (PARTITION BY user_id, session_id ORDER BY "timestamp")

See the demo.
Results:

| user_id | session_id         | timestamp                | t_evolution |
| ------- | ------------------ | ------------------------ | ----------- |
| 180     | 179020080820120904 | 2008-08-20T12:09:04.000Z | 0           |
| 180     | 179020080820120904 | 2008-08-20T12:09:07.000Z | 3           |
| 180     | 179020080820120904 | 2008-08-20T12:10:35.000Z | 91          |
| 180     | 179020080820120904 | 2008-08-20T12:10:37.000Z | 93          |
| 180     | 179020080820120904 | 2008-08-20T12:10:39.000Z | 95          |
| 180     | 179020080820120904 | 2008-08-20T12:10:41.000Z | 97          |
| 180     | 179020080820120904 | 2008-08-20T12:10:43.000Z | 99          |
| 180     | 179020080820120904 | 2008-08-20T12:10:45.000Z | 101         |
| 180     | 179020080820120904 | 2008-08-20T12:10:47.000Z | 103         |
| 180     | 179020080820120904 | 2008-08-20T12:10:49.000Z | 105         |
user_104988280
Related