My suggestion would be to focus on a period data type first and
foremost, as that's something that could be readily used by a lot of
folks. Of particular note, it's difficult to query tables that have
start_time and end_time fields to define a period; it's easy to screw up
the boundary conditions, and it's also hard to make those queries
perform well without going to extra lengths (such as defining a 'bogus'
GiST index on something like box(point(start,start),point(end,end)). And
it's not possible to do that in a way that avoids floating points and
On Sat, Feb 10, 2007 at 12:20:28AM -0700, Warren Turkal wrote:
> Temporal Extensions for PostgreSQL
> by: Warren Turkal
> I would like to see a comprehensive solution to time varying tables (or
> temporal) in PostgreSQL. I specifically want to see suuport for valid-time and
> transacation-time and bitemporal (valid-time and transaction-time) tables. I
> will be defering the descriptions of much of the functionality to Dr. Richard
> Snodgrass's _Developing Time-Oriented Database Applications in SQL_ at .
> mangled pages 30-31 are at .
> a) Functionality
> Dr. Richard T. Snodgrass has worked on defining semantics of temporal very
> completely in several writings. He was also involved in an unsuccessful effort
> to standardize temporal extensions to SQL. I believe his book does a good job
> in presenting the semantics of temporal databases and describing extensions to
> SQL that make the data much more natural with which to work.
> b) How current solutions fall flat
> Current solutions fall flat due to the extreme complexity of implementing
> valid-time and transaction time semantics on tables by adding columns to track
> all of the data. Please see chapter 11 of  for a more complete description
> this complexity. Chapter 12 of  goes on to lay out new syntax for SQL that
> will make dealing with data of this nature much more natural.
> c) Examples
> --create normal table
> CREATE TABLE products
> ( id SERIAL PRIMARY KEY
> , description TEXT
> -- Add valid-time support to the table with granularity of timestamp.
> ALTER TABLE products
> ADD VALIDTIME PERIOD(TIMESTAMP WITH TIMEZONE);
> -- Insert row valid from 2006-01-01 to just before 2007-01-01
> VALIDTIME PERIOD '[2006-01-01 - 2007-01-01)'
> INSERT INTO products
> ( description
> ( 'red ball'
> -- Insert row valid from 2007-01-01 to just before 2008-01-01
> -- Should be smart enough to realize the id=777 does not conflict in this time
> -- of validity.
> VALIDTIME PERIOD '[2007-01-01 - 2008-01-01)'
> INSERT INTO products
> ( id
> , description
> ( 777
> , 'blue ball'
> -- Select history of products with id=777
> SELECT *
> FROM product
> WHERE id=777;
> id | description | valid_period
> 777| red ball | [2006-01-01 - 2007-01-01)
> 777| blue ball | [2007-01-01 - 2008-01-01)
> -- Select current products with id=777
> -- The date when query was run was 2007-02-10.
> SELECT *
> FROM products
> WHERE id=777;
> id | description
> 777| blue ball
> There are many more details in chapter 12 of .
> d) New stuff (dependencies, indices, syntax, libraries)
> One of the base level additions is the PERIOD datatype. I think that
> implementing temporal support is reliant on developing such a type. The
> description of this datatype is laid out in chapter 4 of . The SQL syntax
> present in chapter 12 of . I see this as the first piece that needs to be
> implemented in order to take steps toward a DBMS to supports full temporal
> capabilities. I think that PERIOD can largely reuse the datatime functionality
> for parsing of literals and for comparisons. The RTREE seems to nicely
> incorporate needed indexing of the PERIOD type. The syntax of the parser will
> have to be extended to handle the PERIOD literals and constructor. I believe
> additional libraries will be required.
> There are also extensions to the syntax of table creation, table altering,
> querying, inserting, and updating on temporal tables. These are all discussed
> some detail in chapter 12 of . I don't think that any of these changes will
> require new libraries.
> The semantics of temporal tables and querying them could have a dramatic
> on how things like primary keys and unique constraints work. I would like to
> some comments about this from the community.
> e) See Also
> Addtional resources can be found at Dr. Richard T. Snodgrass's website at ,
> including SQL valid-time table support spec at  and SQL transaction-time
> table support spec at .
> Thoughts? Questions? Comments?
> Warren Turkal (w00t)
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
Jim Nasby [EMAIL PROTECTED]
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings