Re: FW: bitemporal functionality for PostgreSQL

Jeff Davis wrote:
> On Fri, 2008-02-01 at 15:42 +0000, Luke Porter wrote:
>> All
>>
>> Is there an interest in developing bitemporal functionality in
>> PostgreSQL
>>
>
> I am very interested in this topic, and I maintain the pgsql-temporal
> project at:
>
> http://pgfoundry.org/projects/temporal/
>
> It's missing good docs and a few other things that I'd like, but it
> provides a good time interval type, including lots of useful operators,
> and GiST index support functions.
>
> For instance, you can do queries like:
>
> SELECT att1 FROM mytable WHERE during @> '2001-05-11
> 01:01:01'::timestamptz;
>
> which is a simple way to get all records where "during" contains the
> point in time '2001-05-11 01:01:01'. It's also indexable with GiST,
> meaning that query will perform well in a variety of situations.

I don't know what "bitemporal" is all about, but to me, the
pgsql-temporal approach is clearly the most flexible and attractive.
Good interval handling is not limited to just time, BTW, there's other
applications that deal with intervals of other types like floats, though
time intervals are the most common.

We can get pretty far with a pgfoundry project, providing a good
interval data type with operators, but there's a few things that would
need backend support. For example, queries like:

SELECT * FROM mytable1;
timeatt
-------
10 - 20
30 - 40

SELECT * FROM mytable2,
timeatt
-------
15 - 35

SELECT * FROM mytable1
INTERSECT
SELECT * FROM mytable2;
timeatt
-------
15 - 20
30 - 35

as well as declaring uniqueness constraints, so that there's no rows
with overlapping intervals, foreign key references on intervals etc.

I would suggest a book called "Temporal Data and the Relational Model",
by C. J. Date, Hugh Darwen and Nikos A Lorentzos to anyone who's
interested in temporal issues. It presents a very elegant solution to
temporal issues, fully compatible with the relational model. Unlike
"time travel" kind of approaches.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com