Postgres: Calculate the time interval (in seconds) between successive values-CodePudding

I was wondering how we can compute the difference of successive date-times (for example in days or seconds), considering the sample table below:

time                       value
2020-03-30 00:25:10       10112
2020-04-02 08:04:03       45665
2020-04-10 09:55:56       112
2020-04-10 13:12:00       858
2020-04-28 10:15:59       89965
2020-05-30 22:31:02       12

Here is a desired result generated by pandas' diff():

0                NaT
1    3 days 07:38:53
2    8 days 01:51:53
3    0 days 03:16:04
4   17 days 21:03:59
5   32 days 12:15:03

In other words, what is the Postgres equivalent to pandas' diff()?

CodePudding user response：

You can simply subtract a lagged timestamp, and then output as an interval or in the format you desire using to_char. For example:

SELECT "time" - LAG("time") OVER (ORDER BY "time") AS diff,
       TO_CHAR("time" - LAG("time") OVER (ORDER BY "time"), 'DD "days" HH24:MI:SS') AS cdiff
FROM data

Output:

diff                                            cdiff
null                                            null
{"days":3,"hours":7,"minutes":38,"seconds":53}  03 days 07:38:53
{"days":8,"hours":1,"minutes":51,"seconds":53}  08 days 01:51:53
{"hours":3,"minutes":16,"seconds":4}            00 days 03:16:04
{"days":17,"hours":21,"minutes":3,"seconds":59} 17 days 21:03:59
{"days":32,"hours":12,"minutes":15,"seconds":3} 32 days 12:15:03

Demo on db-fiddle

Note if you want a row number as well, you can add one as

ROW_NUMBER() OVER (ORDER BY "time") - 1 AS rownum

You can also get the value in seconds using EXTRACT:

EXTRACT(EPOCH FROM "time" - LAG("time") OVER (ORDER BY "time")) AS seconds

Output

rownum  diff                                            cdiff               seconds
0       null                                            null                null
1       {"days":3,"hours":7,"minutes":38,"seconds":53}  03 days 07:38:53    286733
2       {"days":8,"hours":1,"minutes":51,"seconds":53}  08 days 01:51:53    697913
3       {"hours":3,"minutes":16,"seconds":4}            00 days 03:16:04    11764
4       {"days":17,"hours":21,"minutes":3,"seconds":59} 17 days 21:03:59    1544639
5       {"days":32,"hours":12,"minutes":15,"seconds":3} 32 days 12:15:03    2808903

Demo on db-fiddle

CodePudding user response：

SELECT row_number() OVER w - 1,
       time - lag(time) OVER w
FROM tab
WINDOW w AS (ORDER BY time)
ORDER BY time;

row_number counts the result rows, and lag gets you the value from the previous row in the defined order.