Fun with PostgreSQL and Date/Time

I've been playing around with PostgreSQL 8.3.7 lately and while manipulating intervals realized a few key operators were missing. It turns out it's fairly easy to define operators in PostgreSQL as long as you have an existing function in the database. As a proof of concept I loaded up pl/PgSQL in a working database and created the following functions and operators:

• 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.

The actual code is fairly simple but quite useful. I'm sure it'll perform better if written as a C function but that's a bridge I'll cross when I have to.

Code
Here's the code for the functions:

create function interval_divide (interval, interval) returns double precision as $$
        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;

Here's how to create the appropriate operators using these functions:

create operator / (
        leftarg = interval,
        rightarg = interval,
        procedure = interval_divide
);

create operator % (
        leftarg = interval,
        rightarg = interval,
        procedure = interval_modulo
);

Usage Examples

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

Trac 0.12 brings support for multiple repositories

If you haven't tried trac for a quick but fairly extensive project management / ticketing system / wiki for your project then you really should. Chances are, however, if you've ever used it, you've been irked by the fact that you have to create a new trac instance for every project that you want to er... trac(k) :) It looks like version 0.12 (due out in July 2009) has planned support for multiple repositories in a single instance. This will allow me to e.g. have a separate framework repository and my application repository within the same instance and track changes made to both. I wonder how they'll handle access control to the two repositories.