Steve Crawford wrote:
On Friday 04 February 2005 7:37 am, you wrote:
Hi All, I have stored event records in Postgresql 7.3.4 and now need to calculate the duration between each event in succession. I have "record_id" and a" timestamp without time zone" columns for each event.
What is a good way to calculate the difference in timestamp and store it in the record as duration. I am doing this as part of a java application on RH linux 8.0.
My timestamp is of the form "2005-01-30 07:51:29.149458".
thanks in advance,
Phil
How about:
select (select min(eventtime) from foo b where b.eventtime > a.eventtime)-eventtime from foo a;
Obviously an index on eventtime is indicated but still this query does have the potential to take a long time. It also assumes that event timestamps are unique.
If your timestamps can have the same value and the record_id is monotonically increasing then you can use the same basic technique with the record_id as the selector.
Cheers, Steve
Thanks Steve, Martin...I will give your ideas a try. Phil
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
