[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-sql
Subject: Re: [SQL] Temporal databases
From: "Philippe Lang" <philippe.lang () attiksystem ! ch>
Date: 2007-11-14 13:47:56
Message-ID: 6C0CF58A187DA5479245E0830AF84F4218CF74 () poweredge ! attiksystem ! ch
[Download RAW message or body]
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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic