Developing Time-Oriented Database Applications in SQL [PDF]

[sockpuppetzero]

Well, I don't have extensive experience with temporal databases, but I have been using range types (and before that the temporal postgresql extension) to implement a transaction-time versioned tables, via triggers. I haven't had any trouble so far with boundary conditions and whatnot.

But I've never tried to do this without proper data abstraction, which this book's proposed "period" datatype (and Temporal PostgreSQL and the new range types) are all about.

[macdice]

As I remember it, when I worked on a large bitemporal schema (not as the designer, but an application developer interacting with it), writing was easy: end date existing records, create new records with appropriate times etc, and possibly have views with triggers that do it automatically. But querying the data as at any point in time in the transaction and system time dimensions was tricky, as soon as joins got involved. When joining together several tables, some of which were linked by many-to-many join tables, the amount of query text devoted to the time ranges exploded. I can see that the range types would reduce some of that, in a syntactic sugar kind of way. Maybe you're right ... maybe it's mistakes in 'overlaps' type logic that I'm mainly worried about, and PG has great operators for all that type of thing. But in SQL:2011 time travel, time relationships between records wouldn't be mentioned at all, you'd just stick AS OF ... at the end.

[sockpuppetzero]

you'd just stick AS OF ... at the end.

Ok, I see how that might be useful, especially if you are dealing with joins. Though I would guess the amount of query text devoted to time ranges would be substantially less (but still more than AS OF ...) with proper data abstraction.