From: Philippe Lang <philippe.lang@attiksystem.ch>
To: Keith Carr <klinux@carrhome.eclipse.co.uk>
Cc: pgsql-sql@postgresql.org
Subject: Re: Temporal databases
Date: Wed, 14 Nov 2007 14:47:56 +0100
Message-ID: <6C0CF58A187DA5479245E0830AF84F4218CF74@poweredge.attiksystem.ch> (raw)
References: <6C0CF58A187DA5479245E0830AF84F42337E29@poweredge.attiksystem.ch>
<200711091401.02139.klinux@carrhome.eclipse.co.uk>
<6C0CF58A187DA5479245E0830AF84F4218CF70@poweredge.attiksystem.ch>
<200711141157.11975.klinux@carrhome.eclipse.co.uk>
Keith Carr wrote:
> On Monday 12 November 2007 09:07, you wrote:
>
> 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?
>
> There is no SQL standard for querying temporal databases as far as I
> am aware.
> 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.
>
> 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!
> 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
> integrity) you will be the one left to sort the mess out...... ;)
>
> 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 = '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 = '2007-06-02'::date;
INSERT INTO foo(v, s) VALUES (4, 'fourth line');
DELETE FROM foo WHERE v = 1;
---------------------------------------
Now "SET CURRENT_TIME = '2007-06-01'::date; SELECT * from foo;" would
return:
----------------------
id s
----------------------
1 first line
2 second line
3 third line
----------------------
And "SET CURRENT_TIME = '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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: philippe.lang@attiksystem.ch, klinux@carrhome.eclipse.co.uk
Subject: Re: Temporal databases
In-Reply-To: <6C0CF58A187DA5479245E0830AF84F4218CF74@poweredge.attiksystem.ch>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox