Обсуждение: Temporal databases

Hi,

Does anyone have experience, tips, links, regarding how to build
temporal databases with Postgresql?

Thanks

Philippe

Hi Philippe,
Have a look at the post I made to the pgsql-sql list I made on 6th November 
2007 (day before you posted this). It contains alot of stuff for temporal 
tables within Postgres.
However temporal tables (when done properly) are a very complex subject at the 
best of times. It depends how much you want to integrate them. A good place 
to start is Joe Celko's book "SQL For Smarties" and Richard Snodgrass' 
book "Developing Time-Oriented Database Applications in SQL" - this book is 
no longer in print, but is downloadable (for FREE!) from: 
http://www.cs.arizona.edu/~rts/tdbbook.pdf
Richard Snodgrass is one of the leading experts in the field. I warn you - the 
book is heavy going - but so worth it!!

Keith

On Wednesday 07 November 2007 13:22, Philippe Lang wrote:
> Hi,
>
> Does anyone have experience, tips, links, regarding how to build
> temporal databases with Postgresql?
>
> Thanks
>
> Philippe
>
> ---------------------------(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

pgsql-sql-owner@postgresql.org wrote:
> Hi Philippe,
> Have a look at the post I made to the pgsql-sql list I made on 6th
> November 2007 (day before you posted this). It contains alot of stuff
> for temporal tables within Postgres.
> However temporal tables (when done properly) are a very complex
> subject at the best of times. It depends how much you want to
> integrate them. A good place to start is Joe Celko's book "SQL For
> Smarties" and Richard Snodgrass' book "Developing Time-Oriented
> Database Applications in SQL" - this book is no longer in print, but
> is downloadable (for FREE!) from:
> http://www.cs.arizona.edu/~rts/tdbbook.pdf
> Richard Snodgrass is one of the leading experts in the field. I warn
> you - the book is heavy going - but so worth it!!
>
> Keith

Hi Keith,

I saw your post a few minutes after I posted mine, but I wasn't aware of
the free book of Richard Snodgrass. Thanks for the link!

Have you ever heard about some sort of database extension, or plugin,
that allows using databases "temporally" without have to explicitely add
code in into the schema?

Is there an SQL standard already defined for querying databases
temporally? I think I read about a "Time Connector" or something similar
for Oracle, a few years ago...

Regards,

Philippe Lang

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