Hi,

I'm trying to query two timestamp(0) columns to determine elapsed time. I use the age() function which works fine, but I want to return the information in MINUTES.

So...

sel age(endtime, starttime) returns 02:23:00, for example, or 2 days 03:05:03.

How can I get this to return 143 or 3065.nn - by my examples above - without parsing and substringing or extracting? Is there a clean way to "cast" this to minutes?

Thank you,
Mike

Search Discussions

  • Tom Lane at Mar 26, 2004 at 5:22 pm

    "Vanole, Mike" <mike.vanole@attws.com> writes:
    I'm trying to query two timestamp(0) columns to determine elapsed time. I u=
    se the age() function which works fine, but I want to return the informatio=
    n in MINUTES.=20
    Just subtract (giving an interval), use extract(epoch) to get the
    interval in seconds, then divide by 60 and round (or truncate if you
    prefer).

    regards, tom lane
  • Bruno Wolff III at Mar 26, 2004 at 8:39 pm

    On Fri, Mar 26, 2004 at 12:21:59 -0500, Tom Lane wrote:
    "Vanole, Mike" <mike.vanole@attws.com> writes:
    I'm trying to query two timestamp(0) columns to determine elapsed time. I u=
    se the age() function which works fine, but I want to return the informatio=
    n in MINUTES.=20
    Just subtract (giving an interval), use extract(epoch) to get the
    interval in seconds, then divide by 60 and round (or truncate if you
    prefer).
    To expand on this, while age returns an interval, it will include
    a months part in the interval (if the timestamps are over of month apart)
    and this will mess up your calculation. When you just subtract, you will
    get an absolute time difference that you can reliably convert to minutes.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedMar 23, '04 at 11:54p
activeMar 26, '04 at 8:39p
posts3
users3
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase