Re: Temporal databases - Mailing list pgsql-sql

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