On 08/22/2012 08:34 AM, Gavin Flower wrote: <blockquote cite="mid:50341B0C.4020306@archidevsys.co.nz" type="cite">
About10 years ago, I implemented some temporal features in a database to cope with insurance quotes that had to be
validfor a specified number of days in the future that was invariant with respect to future changes in premiums with
effectivedates within the period of validity of the quote. If anyone is interested, I'll see if I can find my notes and
writeit up (but in a different thread!). <p class="western" style="margin-bottom: 0cm">Cheers,<br /> Gavin</blockquote>
Whatyou mean is not an audit logs, it's a business time. Pavel Stehule in the beginning of this thread gave a link to a
descriptionof SQL2011 design of this feature. Audit logs are more related to system time. For example IBM DB2 uses
followingsyntax for system time (which is mostly SQL2011-conformant).<br /><br /> CREATE TABLE policy ( <br /> id INT
primarykey not null, <br /> vin VARCHAR(10), <br /> annual_mileage INT, <br /> rental_car CHAR(1), <br />
coverage_amtINT, <br /><br /> sys_start TIMESTAMP(12) GENERATED ALWAYS AS ROW BEGIN NOT NULL, <br /> sys_end
TIMESTAMP(12)GENERATED ALWAYS AS ROW END NOT NULL, <br /> trans_start TIMESTAMP(12) GENERATED ALWAYS AS TRANSACTION
STARTID IMPLICITLY HIDDEN, <br /><br /> PERIOD SYSTEM_TIME (sys_start, sys_end) <br /> ); <br /><br /> CREATE TABLE
policy_historyLIKE policy;<br /><br /> ALTER TABLE policy ADD VERSIONING USE HISTORY TABLE policy_history;<br /><br />
Andthe following syntax for querying for historical data.<br /><br /> SELECT coverage_amt <br /> FROM policy FOR
SYSTEM_TIMEAS OF '2010-12-01' <br /> WHERE id = 1111;<br /><br /> SELECT count(*) <br /> FROM policy FOR SYSTEM_TIME
FROM'2011-11-30' TO '9999-12-30' <br /> WHERE vin = 'A1111';<br />
