Content-Class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
Subject: Re: Temporal databases
Date: Wed, 14 Nov 2007 14:47:56 +0100
Message-ID: <6C0CF58A187DA5479245E0830AF84F4218CF74@poweredge.attiksystem.ch>
Thread-Topic: [SQL] Temporal databases
thread-index: AcgmuBVRaUGHoKzbSDeqva8OK/jwUQADAaLQ
References: <6C0CF58A187DA5479245E0830AF84F42337E29@poweredge.attiksystem.ch>
	<200711091401.02139.klinux@carrhome.eclipse.co.uk>
	<6C0CF58A187DA5479245E0830AF84F4218CF70@poweredge.attiksystem.ch>
	<200711141157.11975.klinux@carrhome.eclipse.co.uk>
From: "Philippe Lang" <philippe.lang@attiksystem.ch>
To: "Keith Carr" <klinux@carrhome.eclipse.co.uk>
Cc: <pgsql-sql@postgresql.org>

Keith Carr wrote:
> On Monday 12 November 2007 09:07, you wrote:
>=20
> Hi Philippe,
> I do not know of any "extension" or "plugin" that can be used to give
> database engines "temporality". Temporality will always be a tricky
> subject and it would be impossible to code something general that
> would work for any single situation. For example, on some tables you
> may only want Valid Time recorded, on some tables only Transaction
> Time and in some cases both (or in other cases neither). How would it
> know which one? Also, when it came to queries updatating, deleting or
> even just selecting, which criteria would it now to do this by in
> this situation?       =20
>=20
> There is no SQL standard for querying temporal databases as far as I
> am aware.=20
> This would be considered to be "schema" and so i presume, outside the
> remit of the SQL standards committee. The closest that the SQL
> standards committee will get to this is defining the structure within
> which dates and times will be held and operated on in a "logical"
> sense.   =20
>=20
> Sure this makes temporal databases hard work, but this is the whole
> point of a SQL database and SQL programmers - data integrity for a
> given situation! =20
> Otherwise we may as well be letting the company's accountants go off
> designing databases using Access and spreadsheets?!!!!! And we ALL
> know we don't want that, because when it goes wrong (because there
> was no data  =20
> integrity) you will be the one left to sort the mess out...... ;)
>=20
> Hope this has helped in some way.
> Keith

Hi Keith,

Thanks for your answer.

I haven't been playing with temporal databases at all, so pardon my lack
of precision, but naively I was imaginating something that would more or
less look like:

---------------------------------------
CREATE TABLE foo
(
  id integer,
  s varchar(64)
) WITH TEMPORAL VALID TIME;

SET CURRENT_TIME =3D '2007-06-01'::date;

INSERT INTO foo(v, s) VALUES (1, 'first line');
INSERT INTO foo(v, s) VALUES (2, 'second line');
INSERT INTO foo(v, s) VALUES (3, 'third line');

SET CURRENT_TIME =3D '2007-06-02'::date;

INSERT INTO foo(v, s) VALUES (4, 'fourth line');
DELETE FROM foo WHERE v =3D 1;
---------------------------------------


Now "SET CURRENT_TIME =3D '2007-06-01'::date; SELECT * from foo;" would
return:

----------------------
id   s
----------------------
1    first line
2    second line
3    third line
----------------------

And "SET CURRENT_TIME =3D '2007-06-02'::date; SELECT * from foo;" would
return:

----------------------
id   s
----------------------
2    second line
3    third line
4    fourth line
----------------------


I guess it is much easier to imagine than to develop! For sure I've been
watching "Back to future" too much when I was younger.


Philippe Lang