I'm in the process of adding more historic information to one of my
databases. I've liked the theoretical treatment of the concept in
"Temporal Data and the Relational Model", by Date, Darwen, & Lorentzos.
A lot of it is not realizable without a lot of user defined
types/functions/etc. I was wondering if anyone else has tried to use
their approach as a base for their historical databases in PostGreSQL
and has any "lessons learned" to share.

Thank you.

Eric Nielsen


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

On Oct 14, 2004, at 5:33 AM, Eric D. Nielsen wrote:

I'm working on this as well at the moment. No insights or lessons
learned yet though. I'd also like to see an updated tinterval type
(there's a deprecated one lurking about in the code) that used the
current timestamptz implementation rather than abstime. I'd like to
work on making this datatype and corresponding functions. Need to learn
some c


Michael Glaesemann
grzm myrealbox com


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Eric D. Nielsen wrote:

I have not read the book you mentioned, but I have read a book that may be related. I recommend
looking at:

Developing Time-Oriented Database Applications in SQL
by Richard T. Snodgrass

The book is out of print, but the author has made the PDF available on his website at:
http://www.cs.arizona.edu/people/rts/tdbbook.pdf

One of the main ideas in the book is to define valid time periods to record when information was
true or visible. Valid time periods are implemented by adding 2 columns to a table for the start
date and end date of a period. Much of the book is about how to test for and resolve valid time
period overlap between different rows. Topics include temporal versions of primary keys, inserts,
updates, and deletes. I have implemented these ideas in PostgreSQL. I can talk further about
this if you're interested.

The last part of the book is about adding 2 more columns to a table to define transaction time
periods. Transaction time periods can be used to reconstruct the state of a database at a
specific point in time. I didn't read this part as closely and haven't implemented these ideas.

Hope this helps,
George Essig

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Thanks for the Snodgrass reference, it is rather similar and pre-dates
the book I was looking at. (Same notion of valid/transaction times,
but Date's non-SQL approach) From a quick skim it doesn't address the
distinction Date et al draw between historic and current temporal data;
however it looks very useful for mapping their concepts to SQL.

Eric
On Friday, Oct 15, 2004, at 20:25 US/Eastern, George Essig wrote:


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

--- "Eric D. Nielsen" <nielsene (AT) MIT (DOT) EDU> wrote:

You might want to look at Section 7.5 Temporal Partitioning. One table is used to store current
data and another table is used to store historic data.

George

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

George,

I'd like to thank you for the link as well. It looks really interesting
after reading the front matter.

On Oct 16, 2004, at 10:07 AM, George Essig wrote:

I am very interested in hearing what you've done in PostgreSQL related
to this. I probably should read through the text (isn't PDF wonderful?)
before you go into detail, but a brief overview would be great.

Thanks again for your time.

Cheers,

Michael Glaesemann
grzm myrealbox com


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)