Re: One more question about intervals - Mailing list pgsql-general

From elwood@agouros.de (Konstantinos Agouros)
Subject Re: One more question about intervals
Date
Msg-id elwood.1004905636@news.agouros.de
Whole thread Raw
In response to Re: One more question about intervals  (Konstantinos Agouros <elwood@agouros.de>)
List pgsql-general
In <Pine.LNX.4.30.0111041143020.19169-100000@commandprompt.com> pgsql-general@commandprompt.com ("Command Prompt,
Inc.")writes: 

>On Sun, 4 Nov 2001, Konstantinos Agouros wrote:
>in the largest units possible first, with each unit's field being a
>discrete value; it looks like the EPOCH field is the only one guaranteed
>to give you a combined length of time for the entire duration of the
>interval, so you're definitely on the right track with that.

>You could create a function like this to make your life a little easier:

>CREATE FUNCTION get_hours (interval)
>       RETURNS int4
>       AS 'SELECT round(extract(EPOCH FROM $1) / 3600.0)::integer'
>       LANGUAGE 'sql';

>Depending on whether or not you wanted to round up, down, or to the
>nearest hour, you'd use ceil(), floor(), or round() respectively, on the
>result of the division.
extract epoch from was what I needed to get this where I can work with it.
A time-formatter will come later \:)

Thanks,

Konstantin

>Then you'd just have to do:

>lx=# SELECT get_hours(sum(i)) * 100 || ' Euros' AS cost
>lx-#        FROM my_intervals;
>    cost
>------------
> 7500 Euros
>(1 row)


>Regards,
>Jw.
>--
>jlx@commandprompt.com
>by way of pgsql-general@commandprompt.com



>---------------------------(end of broadcast)---------------------------
>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

--
Dipl-Inf. Konstantin Agouros aka Elwood Blues. Internet: elwood@agouros.de
Otkerstr. 28, 81547 Muenchen, Germany. Tel +49 89 69370185
----------------------------------------------------------------------------
"Captain, this ship will not survive the forming of the cosmos." B'Elana Torres

pgsql-general by date:

Previous
From: "Jeff Lu"
Date:
Subject: Is there an internal timestamp on a table when it was created?
Next
From: Doug McNaught
Date:
Subject: Re: Libpq coding assistance