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(at)commandprompt(dot)com
by way of pgsql-general(at)commandprompt(dot)com