Querying History Tables

A quick method of querying your audit tables for a single point history:

I think if the history table would be expanded with an extra field, giving the end_timestamp of the validity of that record would help a lot.

Using Peter's example:
Your "base" table has two columns, "pk" (not changing) and "data". Rows can be inserted, deleted, and updated (just field "data").
Create a "history" table with five columns: "pk", "timestamp", "end_timestamp", "oldval", "code".

An insert trigger on the base table will insert the following row in the history table: (base.pk, current timestamp, '9999-12-31 23:59:59', base.data, 'I').
A delete trigger will
1) insert (base.pk, current timestamp, current timestamp, base.data, 'D').
2) update most recent record of history with same pk, set "end_timestamp" = current timestamp
An update trigger will
1) insert (base.pk, current timestamp, '9999-12-31 23:59:59', base.data, 'U').
2) update most recent record of history with same pk, set "end_timestamp" = current timestamp
In both cases, "base.data" is the (new) value of base_table.

So any complex query containing a lot of JOINS, would include only an extra "AND :timestamp_requested BETWEEN timestamp AND end_timestamp" condition.

Full article