adding and incrementing date and time (Postgresql)

Question

The below is a table that is meant to show when a media will play. so basically it has a start time (starts), the length of the track (clip_length), and when it ends (ends = starts + clip_length), and finally the position of the track.

|starts              | ends                 |position   |file_id| clip_length
|2013-08-30 22:00:00 | 2013-08-30 22:03:08  |0          |16     |00:03:08.081768
|2013-08-30 22:03:08 | 2013-08-30 22:06:33  |1          |17     |00:03:25.436485
|2013-08-30 22:06:33 | 2013-08-30 22:09:07  |2          |7      |00:02:33.79968
|2013-08-30 22:09:07 | 2013-08-30 22:12:21  |3          |3      |00:03:14.020273
|2013-08-30 22:12:21 | 2013-08-30 22:15:31  |4          |8      |00:03:10.466689

what i want to do is to add a record, at say position =2 , shown bellow. I have been able to increment the positions, how ever the problem lies with the fact that the times are all messed up.

|starts              | ends                 |position   |file_id|clip_length
|2013-08-30 22:00:00 | 2013-08-30 22:03:08  |0          |16     |00:03:08.081768
|2013-08-30 22:03:08 | 2013-08-30 22:06:33  |1          |17     |00:03:25.436485
|2013-08-30 22:06:33 | 2013-08-30 22:09:07  |2          |7      |00:02:33.79968
|2013-08-30 22:06:33 | 2013-08-30 22:11:03  |3          |1      |00:04:30.006958
|2013-08-30 22:09:07 | 2013-08-30 22:12:21  |4          |3      |00:03:14.020273
|2013-08-30 22:12:21 | 2013-08-30 22:15:31  |5          |8      |00:03:10.466689

so it possible to use the first start time.. as point 00, and add clip_length to starts and save in ends, for the first one. then for the second one use the first ends value as the starts and do this recursively till the end (following the positions) .

thanks in advance..

Answer 1

SQL Fiddle

update clip c
set
    starts = s.starts,
    ends = s.ends
from (
    select
        starts,
        starts + clip_length as ends,
        file_id,
        position
    from (
        select
            '2013-08-30 22:00:00'::timestamp
            + sum(clip_length) over(order by position)
            - clip_length as starts,
            clip_length,
            file_id,
            position
        from clip
    ) s
) s
where c.file_id = s.file_id