Re: Proposal - temporal contrib module

2009/10/29 Scott Bailey <artacus(at)comcast(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>