More Fun with PostgreSQL Date/Time

I got a number of comments from sasha2048 about the modulo, division and remainder operators for the interval data types in a previous blog entry. After playing with all the suggestions I figured it would be best to devote another blog post to the revised code for the functions and operators. The main quibble sasha2048 had with the functions was their precision - they were only good for intervals expressed in seconds and weren't able to handle more precise intervals e.g. in the millisecond range. Here, then, are the updated functions that have the following features:

1. The concept of a modulo operator for double precision numbers where a % b = (a - floor(a/b)*b)
2. Updated interval_divide and interval_modulo functions that store the extracted epoch from a timestamp into a double precision variable instead of an integer
3. Made all functions immutable and "return null on null input"
4. Added a default value for the "precision" argument in the round function - it's now set to 1 second so unless you specify a precision level, all round calls will round an interval to the nearest second.

-- Functions

create function interval_divide (interval, interval) returns double precision as $$

declare

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

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

begin

return firstEpoch / secondEpoch;

end

$$ language plpgsql immutable returns null on null input;

create function double_precision_modulo (double precision, double precision) returns integer as $$

begin

return ($1 - floor($1 / $2) * $2);

end

$$ language plpgsql immutable returns null on null input;

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

declare

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

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

begin

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

end

$$ language plpgsql immutable returns null on null input;

create function round (interval, interval default '1 second'::interval) returns interval as $$

declare

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

begin

return $2 * quantumNumber;

end

$$ language plpgsql immutable returns null on null input;

-- Operators

create operator % (

leftarg = double precision,

rightarg = double precision,

procedure = double_precision_modulo

);

create operator / (

leftarg = interval,

rightarg = interval,

procedure = interval_divide

);

create operator % (

leftarg = interval,

rightarg = interval,

procedure = interval_modulo

);