Thread: Another date / time question

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

"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

On Fri, Mar 26, 2004 at 12:21:59 -0500,
  Tom Lane <tgl@sss.pgh.pa.us> 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.