I would like to do
SELECT * TABLE t (using data from 1st of march 2012)
I already have a nice audit trail of all tables in the database. It basically makes a copy of all rows that change, storing all columns of the changed row in an hstore column. This means that we have the data available, but I am unsure of whether it is the best way to store the data for a database time machine.
To give you more context, we are creating accounting software. This means that we need to be able to recreate all reports which we have offered to the customer (they can see them on our website and get updated continuously).
What sorts of problems do you anticipate that I will run into with this approach? Is there a better approach?
Here is my favorite approach:
Now you can write a point-in-time query, even with joins:
select g.groupname, p.productname, p.price from products_hist p, product_groups_hist g where p.id = g.id and p.start <= now() and now() < p.end and g.start <= now() and now() < g.end
BETWEEN, use an exclusive upper-bound. What you're doing will include 2 rows in some cases.
endis best thought of conceptually as
null, which it should probably be stored as (although this would require additional checks). Note that ending values are always derived values. — Dec 15, 2014 at 13:08
External links referenced by this document: