How do you best sum the differences of a range of dates in the same column between interleaving rows? I have a Datetime column and want to calculate the difference between Rows. I want the difference in Seconds. This questions is not about how to get a difference between 2 timestamps, but is more focused on how to most efficiently calculate between rows on the same table. In my case each row has a datetime eventype that links 2 rows together logically.
Details Related to how to group the eventtypes of start and end. ( Andriy M's Question) Starts and Ends "should" be consecutive. If a Start does not have a subsequent end it should be left out of the sum. Moving to the next Start to see if it has a end. Only consecutive Start - End Pairs should be added to the sum of the total seconds.
Working in postgresql 9.x...
Example data in table;
eventtype, eventdate START, 2015-01-01 14:00 END, 2015-01-01 14:25 START, 2015-01-01 14:30 END, 2015-01-01 14:43 START, 2015-01-01 14:45 END, 2015-01-01 14:49 START, 2015-01-01 14:52 END, 2015-01-01 14:55
Note, All Start and End Dates will be sequential.
Here's my first attempt. Seems to be working.
SELECT -- starts.* SUM(EXTRACT(EPOCH FROM (eventdate_next - eventdate))) AS duration_seconds FROM ( WITH x AS ( SELECT *, dense_rank() OVER (ORDER BY eventdate) AS rnk FROM table AND eventdate > '2015-01-01 00:00:00.00' AND eventdate < '2016-01-01 23:59:59.59' ) SELECT x.eventdate, x.eventtype, y.eventdate AS eventdate_next, y.eventtype AS eventtype_next FROM x LEFT JOIN (SELECT DISTINCT eventdate, eventtype, rnk FROM x) y ON y.rnk = (x.rnk + 1) ORDER BY x.eventdate ) starts WHERE eventtype = 'START' GROUP BY eventtype
My first attempt is based on a great example from stackoverflow Postgres 9.1 - Getting the next value
Note; You can comment the GROUP BY and the SUM and un-comment the starts.* to get a record for each individual duration going into the sum.