Phil - you can subtract two timestamps very easily in Postgres, giving
you an interval. You can also use the age() function. You probably
already knew that, though.
Sounds like your bigger problem is how to figure out the "in succession"
bit. Are you trying to figure the relative difference between the
timestamp in row 2 vs. row 1, row 3 vs. row 2, row 4 vs. row 3, etc? Or
is your second timestamp always going to be now()?
BTW, is it necessary to store the difference? It's generally bad
practice to store something you can compute unless the table is so large
that the disk costs and CPU time of storing/indexing the data outweigh
the CPU costs to calculate it. OTOH, if, when you insert the new row
you find the maximum timestamp already in the table, you can lick the
problem I highlighted above as the max(event_date) becomes your second
timestamp.
From your Java pgm, call a function in Postgres and let Postgres do all
the work.
Rgds,
Bret
> -----Original Message-----
> From: pgsql-novice-owner@postgresql.org
> [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of phil campaigne
> Sent: Friday, February 04, 2005 10:35 AM
> To: pgsql-novice@postgresql.org
> Subject: [NOVICE] Duration betweeen 2 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
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to
> majordomo@postgresql.org)
>