I maintain the project "Temporal PostgreSQL" on pgfoundry:
http://pgfoundry.org/projects/temporal
Reference docs available here:
http://temporal.projects.postgresql.org/reference.html
This project provides a data type called "period" (formerly t_interval).
This type is an interval in the mathematical sense, that is, it has a
beginning time and end time (timestamptz, to be specific). This is not
like the SQL type "interval" which is not anchored at any specific point
in time.
This is valuable for many applications that need to be able to
manipulate time. This functionality is sometimes called "bi-temporal",
because people sometimes represent it as two timestamps in two columns.
Representing the information in one column of "periods" has important
advantages such as:* It's much simpler and less error-prone to write queries expressing
"overlaps", "contains", "intersection", etc.* We can effectively index this type with GiST. There's no effective
way to index two separate timestamp columns.
During PGCon 2008, several people encouraged me to submit the code for
inclusion in the core.
The advantages of including it in core are that some features can't be
done from pgfoundry, such as:* temporal foreign keys* temporal joins* syntax like "ALTER TABLE ... ADD LOG".
On the other hand, I don't currently have proposals for any of those
things. And including in core has the usual drawbacks, like waiting for
a new PostgreSQL release just to get some improvement for the data type.
1. Should it be included in core, or remain on pgfoundry?
2. If it should be included in core, I'd like to know if any changes
should be made to the API, available operators, or the names of anything
(see the reference docs). The current name of the type is "period" to
avoid confusion with SQL's misnamed "interval" type. The operators are
mostly self-explanatory, but I'm open to suggestion for better names for
those, too.
3. I'd like to get some help with details like the optimal GiST
picksplit function to use, and selectivity functions, and analyze
functions.
4. Should we replace the undocumented type "tinterval"?
Regards,Jeff Davis
