2009/10/29 Scott Bailey <arta...@comcast.net>: > I would like to add a temporal contrib module. The most important piece > would be adding a period data type and some support functions. Jeff Davis > and I both have temporal projects on pgFoundry, and we've been collaborating > for a while. But there are some areas we'd like to get some advice on. >
I thing, so it is very good idea. Temporar operation is very common task. Regards Pavel Stehule > Disk format - A period can be represented as [closed-closed], (open-open), > [closed-open) or (open-closed] intervals. Right now we convert these to the > most common form, closed-open and store as two timestamptz's. > > Nulls - A common use case for periods is for modeling valid time. Often the > end point is not known. For instance, you know when an employee has been > hired but the termination time typically wouldn't be known ahead of time. We > can either represent these with a null end time or with infinity. But I'm > not sure how to deal with them. Obviously we can test for containment and > overlap. But what about length or set operations? > > Non-contiguous Sets - A period defines a contiguous set of time. But many > times we need to work with non-contiguous sets (work shifts in a week, bus > schedules, etc). Right now, I'm using period arrays. But period arrays can > contain overlapping and adjacent periods. And we have no way to indicate > that a period array has been coalesced into a non-contiguous set. And what > indexing strategies could be used with non-contiguous sets? > > Temporal Keys - We need two types of temporal keys. A primary key, exclusion > type prevents overlap so someone isn't at two places at the same time. And a > foreign key, inclusion type so we can check that the valid time of a child > is contained with in the valid time of the parent. Jeff is working on the > former, but there is no easy way to do the latter. > > > There is actually a lot of theory out there but very few implementations. > Although not an official standard, we try to follow the TSQL2 spec pretty > closely. Further reading: > > Developing Time-Oriented Database Applications - Snodgrass > http://www.cs.arizona.edu/~rts/tdbbook.pdf > > TSQL2 spec ftp://ftp.cs.arizona.edu/tsql/tsql2/spec.pdf > > Temporal Data and the Relational Model - Date et al > http://books.google.com/books?isbn=1558608559 > > Dozens of publications > http://timecenter.cs.aau.dk/pub.htm > > > Regards, > > Scott Bailey > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers > -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers