- interval_divide (used to power the / operator for intervals): Divides one interval by another and returns a real. Its purpose is to tell you how many interval2-sized chunks of time exist in interval1.
- interval_modulo (used to power the % operator for intervals): Divides one interval by another and returns the remainder as an interval. Its purpose is to tell you how much time would be left over if you fit in as many interval2 sized chunks of time in interval1. You can also use it to determine whether interval1 can perfectly fit a whole number of interval2-size chunks of time.
- round: Rounds one interval to the nearest value that will fit a whole number of interval2-sized chunks of time.

Code

Here's the code for the functions:

create function interval_divide (interval, interval) returns double precision as $$Here's how to create the appropriate operators using these functions:

declare

firstEpoch constant integer := extract(epoch from $1);

secondEpoch constant integer := extract(epoch from $2);

begin

return firstEpoch::double precision / secondEpoch::double precision;

end

$$ language plpgsql;

create function interval_modulo (interval, interval) returns interval as $$

declare

firstEpoch constant integer := extract(epoch from $1);

secondEpoch constant integer := extract(epoch from $2);

begin

return (firstEpoch % secondEpoch) * '1 second'::interval;

end

$$ language plpgsql;

create function round (interval, interval) returns interval as $$

declare

quantumNumber constant real := round($1 / $2);

begin

return $2 * quantumNumber;

end

$$ language plpgsql;

create operator / (Usage Examples

leftarg = interval,

rightarg = interval,

procedure = interval_divide

);

create operator % (

leftarg = interval,

rightarg = interval,

procedure = interval_modulo

);

=> select '1 hour'::interval / '5 minutes'::interval;

?column?

----------

12

(1 row)

=> select '1 hour'::interval / '7 minutes'::interval;

?column?

----------

8.57143

(1 row)

=> select '1 hour'::interval % '7 minutes'::interval;

?column?

----------

00:04:00

(1 row)

=> select '1 hour'::interval % '5 minutes'::interval;

?column?

----------

00:00:00

(1 row)

=> select round('1 hour'::interval, '7 minutes'::interval);

round

----------

01:03:00

(1 row)

=> select '1 hour 3 minutes'::interval / '7 minutes'::interval;

?column?

----------

9

(1 row)