Duration between two timestamps

On Fri, Feb 04, 2005 at 10:37:52AM -0500, phil campaigne 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.

Have you tried just subtracting them? That will give you a value of
type "interval" which you can then store...

Hope this helps,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

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