After being frustrated with the inflexible output of intervals, I've written
a pl/pgsql function to do what I want, and hopefully some other people might
find it useful.
Output is a string that matches the output format of an interval as closely
as possible, but rather than counting days as a fixed 24-hours, it
recalculates days based on the number of hours in the desired 'day'.
For example, this is very useful for summing up work time and outputting a
number of 'work days' rather than 24 hour periods. The number of hours in
the new 'day' can be any double precision value (in hours), such as to
account for my 7.5-hour billable days:
# select dur as interval, workdays(dur,7.5) from worklog;
interval | workdays
----------------+-------------------
-00:10:00 | -00:10:00
-00:10:00 | -00:10:00
-00:15:00 | -00:15:00
-04:00:00 | -04:00:00
-04:00:00 | -04:00:00
-04:00:00 | -04:00:00
-13:00:00 | -1 days -05:30:00
-02:00:00 | -02:00:00
-00:30:00 | -00:30:00
-01:00:00 | -01:00:00
-00:15:00 | -00:15:00
-02:00:00 | -02:00:00
-03:25:00 | -03:25:00
-00:30:00 | -00:30:00
-00:30:00 | -00:30:00
1 day 08:30:00 | 4 days 02:30:00
-00:05:00 | -00:05:00
-00:10:00 | -00:10:00
(18 rows)
# select sum(dur) as "sum of interval", workdays(sum(dur),7.5) as "workdays of
sum" from worklog where dur < 0;
sum of interval | workdays of sum
-------------------+-------------------
-1 days -12:00:00 | -4 days -06:00:00
(1 row)
Formatting consistency:
# select foo as interval, workdays(foo,7.5) from intest order by foo;
interval | workdays
-------------------+-------------------
-1 days -04:00:00 | -3 days -05:30:00
-1 days | -3 days -01:30:00
-20:00:00 | -2 days -05:00:00
-07:30:00 | -1 days
00:00:00.00123 | 00:00:00.00123
00:15:10 | 00:15:10
07:15:10 | 07:15:10
07:30:00 | 1 day
07:45:10 | 1 day 00:15:10
14:59:59 | 1 day 07:29:59
15:00:00 | 2 days
1 day | 3 days 01:30:00
4 days 04:00:00 | 13 days 02:30:00
100 days | 320 days
365 days | 1168 days
20 years | 23376 days
(16 rows)
The only caveat, currently, is that the output of workdays() is a string, so
you can't effectively sort it, or perform arithmetic or comparisons on it.
You must perform these operations on the real interval _before_ running it
though workdays().
CREATE OR REPLACE FUNCTION workdays (interval, double precision) RETURNS text AS '
DECLARE
in_epoch double precision;
out_days double precision;
out_hours double precision;
out_minutes double precision;
out_seconds double precision;
temp double precision;
out character varying;
negative boolean;
BEGIN
--- Copyright 2004 Webcon, Inc. Written by Ian Morgan.
--- Distributed under the GNU Public License.
in_epoch := extract(EPOCH FROM $1);
negative := ''f'';
if in_epoch < 0 then
negative := ''t'';
in_epoch := in_epoch * -1;
end if;
out_days := floor(in_epoch / ($2 * 60 * 60));
temp := in_epoch - (out_days * ($2 * 60 * 60));
out_hours := floor(temp / 3600);
temp := temp - (out_hours * 3600);
out_minutes := floor(temp / 60);
out_seconds := temp - (out_minutes * 60);
out := '''';
if negative = ''t'' then
out_days := out_days * -1;
out_hours := out_hours * -1;
end if;
if out_days != 0 then
out := out || out_days;
if (out_days = 1) then
out := out || '' day'';
else
out := out || '' days'';
end if;
end if;
if (out_hours != 0) or (out_minutes != 0) or (out_seconds != 0) then
if out_days != 0 then
out := out || '' '';
end if;
out := out || to_char(out_hours, ''FM09'') || '':'' || to_char(out_minutes, ''FM09'') || '':'';
if (out_seconds = floor(out_seconds)) then
out := out || to_char(out_seconds, ''FM09'');
else
out := out || to_char(out_seconds, ''FM09.999999'');
end if;
end if;
return out;
END;
' LANGUAGE plpgsql;
If anyone has improvements or optimizations, I'd be glad to see them.
Regards,
Ian Morgan
--
-------------------------------------------------------------------
Ian E. Morgan Vice President & C.O.O. Webcon, Inc.
imorgan at webcon dot ca PGP: #2DA40D07 www.webcon.ca
* Customized Linux Network Solutions for your Business *
-------------------------------------------------------------------
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
