Tuesday, June 30, 2009

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)

7 comments:

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

    ReplyDelete
  2. You have 1 mistake. firstEpoch and secondEpoch must be declared as DOUBLE PRECISON, not as integer.
    E.g. "extract(epoch from interval '1 msec')" gives us "0.001".

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

    ReplyDelete
  4. sasha,

    Interesting 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? :)

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

    ReplyDelete
  6. This comment has been removed by the author.

    ReplyDelete
  7. Like that:
    create function double_precision_modulo(double precision, double precision)...
    ...
    return $1-floor($1/$2)*$2;
    ...

    ReplyDelete