pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedTemporal databases
4+ messages / 2 participants
[nested] [flat]
* Temporal databases
@ 2007-11-07 13:22 Philippe Lang <philippe.lang@attiksystem.ch>
2007-11-09 14:01 ` Re: Temporal databases Keith Carr <klinux@carrhome.eclipse.co.uk>
0 siblings, 1 reply; 4+ messages in thread
From: Philippe Lang @ 2007-11-07 13:22 UTC (permalink / raw)
To: pgsql-sql
Hi,
Does anyone have experience, tips, links, regarding how to build
temporal databases with Postgresql?
Thanks
Philippe
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Temporal databases
2007-11-07 13:22 Temporal databases Philippe Lang <philippe.lang@attiksystem.ch>
@ 2007-11-09 14:01 ` Keith Carr <klinux@carrhome.eclipse.co.uk>
2007-11-12 09:07 ` Re: Temporal databases Philippe Lang <philippe.lang@attiksystem.ch>
0 siblings, 1 reply; 4+ messages in thread
From: Keith Carr @ 2007-11-09 14:01 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Temporal databases
2007-11-07 13:22 Temporal databases Philippe Lang <philippe.lang@attiksystem.ch>
2007-11-09 14:01 ` Re: Temporal databases Keith Carr <klinux@carrhome.eclipse.co.uk>
@ 2007-11-12 09:07 ` Philippe Lang <philippe.lang@attiksystem.ch>
2007-11-14 13:47 ` Re: Temporal databases Philippe Lang <philippe.lang@attiksystem.ch>
0 siblings, 1 reply; 4+ messages in thread
From: Philippe Lang @ 2007-11-12 09:07 UTC (permalink / raw)
To: Keith Carr <klinux@carrhome.eclipse.co.uk>; pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Temporal databases
2007-11-07 13:22 Temporal databases Philippe Lang <philippe.lang@attiksystem.ch>
2007-11-09 14:01 ` Re: Temporal databases Keith Carr <klinux@carrhome.eclipse.co.uk>
2007-11-12 09:07 ` Re: Temporal databases Philippe Lang <philippe.lang@attiksystem.ch>
@ 2007-11-14 13:47 ` Philippe Lang <philippe.lang@attiksystem.ch>
0 siblings, 0 replies; 4+ messages in thread
From: Philippe Lang @ 2007-11-14 13:47 UTC (permalink / raw)
To: Keith Carr <klinux@carrhome.eclipse.co.uk>; +Cc: 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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2007-11-14 13:47 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-11-07 13:22 Temporal databases Philippe Lang <philippe.lang@attiksystem.ch>
2007-11-09 14:01 ` Keith Carr <klinux@carrhome.eclipse.co.uk>
2007-11-12 09:07 ` Philippe Lang <philippe.lang@attiksystem.ch>
2007-11-14 13:47 ` Philippe Lang <philippe.lang@attiksystem.ch>
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