On Sun, 4 Nov 2001, Konstantinos Agouros wrote:
>Actually what I would need is date_part(EPOCH) since then I am sure that it is
>the seconds and I have to multiply with EURO/3600. The extract-stuff would
>ignore minutes or I would have to add these also. The problem is I have to
>reconstruct the hours in the format by hand and it would be nice to have the
>database do this by itself \:)
Well, once the minutes field goes over 60, it gets added to the hours.
Intervals appears to behave in such a way as to describe a length of time
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.
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