2012/8/22 Vlad Arkhipov <arhi...@dc.baikal.ru>: > On 08/22/2012 08:34 AM, Gavin Flower wrote: > > About 10 years ago, I implemented some temporal features in a database to > cope with insurance quotes that had to be valid for a specified number of > days in the future that was invariant with respect to future changes in > premiums with effective dates within the period of validity of the quote. If > anyone is interested, I'll see if I can find my notes and write it up (but > in a different thread!). > > Cheers, > Gavin > > What you mean is not an audit logs, it's a business time. Pavel Stehule in > the beginning of this thread gave a link to a description of SQL2011 design > of this feature. Audit logs are more related to system time. For example IBM > DB2 uses following syntax for system time (which is mostly > SQL2011-conformant). > > CREATE TABLE policy ( > id INT primary key not null, > vin VARCHAR(10), > annual_mileage INT, > rental_car CHAR(1), > coverage_amt INT, > > sys_start TIMESTAMP(12) GENERATED ALWAYS AS ROW BEGIN NOT NULL, > sys_end TIMESTAMP(12) GENERATED ALWAYS AS ROW END NOT NULL, > trans_start TIMESTAMP(12) GENERATED ALWAYS AS TRANSACTION START ID > IMPLICITLY HIDDEN, > > PERIOD SYSTEM_TIME (sys_start, sys_end) > ); > > CREATE TABLE policy_history LIKE policy; > > ALTER TABLE policy ADD VERSIONING USE HISTORY TABLE policy_history; > > And the following syntax for querying for historical data. > > SELECT coverage_amt > FROM policy FOR SYSTEM_TIME AS OF '2010-12-01' > WHERE id = 1111; > > SELECT count(*) > FROM policy FOR SYSTEM_TIME FROM '2011-11-30' TO '9999-12-30' > WHERE vin = 'A1111';
I like this design - it is simple without other objects Regards Pavel -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers