Thread: Has anyone tried Date/Darwen/Lorentzos's model for temporal data?
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
On Oct 14, 2004, at 5:33 AM, Eric D. Nielsen wrote: > 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. 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
Eric D. Nielsen wrote: > 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. 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
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: > Eric D. Nielsen wrote: > >> 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. > > 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
--- "Eric D. Nielsen" <nielsene@MIT.EDU> wrote: > 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 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
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: > > --- "Eric D. Nielsen" <nielsene@MIT.EDU> wrote: > >> 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 > > 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. 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