It might be useful to look at the capabilities of the Informix Timeseries Datablade (http://www-01.ibm.com/software/data/informix/blades/) if you want to look at ways of enhancing the temporal data capabilities of Postgres. Cheers, Brent Brent Wood DBA/GIS consultant NIWA, Wellington New Zealand >>> Chris Browne <cbbrowne@xxxxxxx> 12/16/08 10:05 AM >>> rshepard@xxxxxxxxxxxxxxx (Rich Shepard) writes: > > [2] Strangely enough -- to me, at least -- the lack of full support for > date- and time-based SQL in database tools such as PostgreSQL is puzzling. > Virtually all business-related databases (think accounting systems as a > prime example) depend on dates. So do many scientific databases. The support for temporality in PostgreSQL seems above average as far as I can see... PostgreSQL has pretty nice time types between the timestamptz type and interval. What strikes me as being missing is the ability to create temporally-aware foreign keys. That is, suppose the schema is: create table1 ( nearly_pk integer not null, from_date timestamptz not null default now(), to_date timestamptz not null default 'Infinity', constraint dating_t1 check (from_date < to_date) -- probably some other data... ); I'd like to be able to do two more things: a) Treat the date range as part of the primary key (which isn't forcibly hard), b) Have references to table1 that point to the time range for the "nearly_pk" value but which are a little more liberal with the dates. create table2 ( t2pk integer primary key, nearly_pk integer not null, from_date timestamptz not null default now(), to_date timestamptz not null default 'Infinity', -- And have a "foreign key" that requires that -- for tuple in table2 the combination (nearly_pk, from_date, to_date) -- is *contained* by relevant ranges of (nearly_pk, from_date, to_date) -- on table1 foreign key (nearly_pk) references table1(nearly_pk) with temporal (table2.from_date, table2.to_date) contained_by (table1.from_date, table1.to_date) ); I don't think the syntax there is necessarily quite right; I'm just hoping to express the idea successfully. I could presumably do this with a trigger; have been trying to avoid that thus far. There are, of course, other ways of treating temporality; that is part of why it's early to treat this approach as worth putting into syntax. -- output = ("cbbrowne" "@" "acm.org") http://cbbrowne.com/info/finances.html "When the grammar checker identifies an error, it suggests a correction and can even makes some changes for you." -- Microsoft Word for Windows 2.0 User's Guide, p.35: -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general NIWA is the trading name of the National Institute of Water & Atmospheric Research Ltd. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
