Обсуждение: extracting total amount of time from an interval

On 1 July 2010 12:53, Andrew Geery <andrew.geery@gmail.com> wrote:
> Is there a way to extract the total amount of time in a given unit from an
> interval?  For example, I would like to know the total number of
> milliseconds in an interval.
> The expression
> select extract('seconds' from '5 minutes'::interval)
> returns the value of the seconds field (0) in the interval; not the total
> number of seconds in the interval.  Is there a function like extract, but
> which extracts the value not *from* the given field, but *in* the given
> units (the opposite of the justify* functions)?
> I started using the interval type because it a) looks nice in result sets
> (e.g., 01:02:00); and b) can do arithmetic with timestamps.  However, since
> I'm going to need to be able to convert to and from milliseconds for my
> application, I'm wondering if I wouldn't be better off just using a numeric
> type for storing the intervals in seconds (the intervals would be no more
> than a day total).  Thoughts?
> Thanks
> Andrew

You can use:

select extract(epoch from '5 minutes'::interval);

Regards

Thom

In response to Andrew Geery :
> Is there a way to extract the total amount of time in a given unit from an
> interval?  For example, I would like to know the total number of milliseconds
> in an interval.
>
> The expression
>
> select extract('seconds' from '5 minutes'::interval)
>
> returns the value of the seconds field (0) in the interval; not the total
> number of seconds in the interval.  Is there a function like extract, but which
> extracts the value not *from* the given field, but *in* the given units (the
> opposite of the justify* functions)?

You can use extract:

test=*# select extract(epoch from '5 minutes'::interval);
 date_part
-----------
       300

Regards, Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431  2EB0 389D 1DC2 3172 0C99