Re: RFC: Temporal Extensions for PostgreSQL - Mailing list pgsql-hackers

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
their errors.

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 
> T.
> Snodgrass's _Developing Time-Oriented Database Applications in SQL_ at [1]. 
> The
> mangled pages 30-31 are at [2].
> 
> 
> 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 [1] for a more complete description 
> of
> this complexity. Chapter 12 of [1] 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
>                )
>           VALUES
>                ( '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
>                )
>           VALUES
>                ( 777
>                , 'blue ball'
>                );
> 
> -- Select history of products with id=777
> VALIDTIME
>    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 [1].
> 
> 
> 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 [1]. The SQL syntax 
> is
> present in chapter 12 of [1]. 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 
> any
> 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 
> in
> some detail in chapter 12 of [1]. 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 
> affect
> on how things like primary keys and unique constraints work. I would like to 
> get
> some comments about this from the community.
> 
> 
> e) See Also
> 
> Addtional resources can be found at Dr. Richard T. Snodgrass's website at [3],
> including SQL valid-time table support spec at [4] and SQL transaction-time
> table support spec at [5].
> 
> Thoughts? Questions? Comments?
> 
> [1]http://www.cs.arizona.edu/~rts/tdbbook.pdf
> [2]http://www.cs.arizona.edu/~rts/pp30-31.pdf
> [3]http://www.cs.arizone.edu/~rts/
> [4]ftp://ftp.cs.arizona.edu/tsql/tsql2/sql3/mad146.pdf
> [5]ftp://ftp.cs.arizona.edu/tsql/tsql2/sql3/mad147.pdf
> 
> Thanks,
> wt
> -- 
> Warren Turkal (w00t)
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
> 
>                http://www.postgresql.org/docs/faq
> 

-- 
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)