Thread: Duration between two timestamps

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

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/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

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