Proposal: temporal extension "period" data type
От | Jeff Davis |
---|---|
Тема | Proposal: temporal extension "period" data type |
Дата | |
Msg-id | 1211785360.26526.98.camel@jdavis обсуждение исходный текст |
Ответы |
Re: Proposal: temporal extension "period" data type
Re: Proposal: temporal extension "period" data type Re: Proposal: temporal extension "period" data type |
Список | pgsql-hackers |
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
В списке pgsql-hackers по дате отправления: