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

Search Discussions

  • Schuhmacher, Bret at Feb 9, 2005 at 11:04 am
    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
    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)

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedFeb 4, '05 at 3:37p
activeFeb 9, '05 at 11:04a
posts2
users2
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase