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)
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 |