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

Thanks, very useful. (Actually, I don't understand why these operators aren't builtin.)

ReplyDeleteYou have 1 mistake. firstEpoch and secondEpoch must be declared as DOUBLE PRECISON, not as integer.

ReplyDeleteE.g. "extract(epoch from interval '1 msec')" gives us "0.001".

Also, I'd like to add "IMMUTABLE" (this should give some optimization, isn't it).

ReplyDeletesasha,

ReplyDeleteInteresting observations. I'll need to try them out in my lab to ensure I have valid syntax and will then update this blog entry.

Meanwhile, I noticed that making firstEpoch and secondEpoch double precision variables will make it impossible to use the % operator in the interval_module function. It would appear that a more general form of that function would require more thought. Perhaps that's why it's not part of the default operator set? :)

The thing that should be done before implementing the '%' operator for intervals, is implementing the '%' operator for reals :).

ReplyDeleteThis comment has been removed by the author.

ReplyDeleteLike that:

ReplyDeletecreate function double_precision_modulo(double precision, double precision)...

...

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

...