Alternate interval output function - Mailing list pgsql-general

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  *
-------------------------------------------------------------------