Re: One more question about intervals

On Sun, 4 Nov 2001, Command Prompt, Inc. wrote:
>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.

And actually, if you *really* wanted to make your life easier, you could:

-- Build a function which combines a passed integer with a derived hour:
CREATE FUNCTION get_hours (int4, interval) RETURNS int4
       AS 'SELECT round(extract(EPOCH FROM $2) / 3600.0)::integer + $1'
       LANGUAGE 'sql';

-- Get the sum of hours on an interval, with an aggregate:
CREATE AGGREGATE sum_hours (BASETYPE = interval, SFUNC = get_hours,
                            STYPE = int4, INITCOND = 0);

-- Make a little cost-formatter, which takes the hours billed, and
-- the euro/hour rate.
CREATE FUNCTION get_euros(int4, int4) RETURNS text
       AS 'SELECT $1 * $2 || '' Euros''' LANGUAGE 'sql';

And voila:

lx=# SELECT get_euros(sum_hours(i), 100) AS euros FROM my_intervals;
   euros
------------
 7500 Euros
(1 row)

I think I'm maybe feeling overhelpful today. ;)


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