Introduction to Temporal Data Management
Time is one of the most critical aspects of almost any database. In this talk I will present some of the peculiarities of time data and strategies for organizing it. I will also demonstrate better tools for managing temporal data -- PostgreSQL offers the best tools of any SQL system!
The most important thing about managing time -- and databases in general -- is that we must model reality, not what we wish reality to be. Days, years, seasons, and timezones are all a part of that reality that we can't ignore.
We also need more effective tools than standard SQL offers. Singular points in time are awkward to work with when discussing even basic concepts, like the period of time a class is being taught. We need a better "noun", and the PERIOD data type offers that.
Data integrity for temporal data is tricky at best. How do you prevent a schedule conflict -- that is, one person being scheduled for two places at overlapping periods of time -- from being accepted into your database? PostgreSQL 9.0 offers a new tool -- Exclusion Constraints -- that is the best tool currently available to enforce constraints such as this.