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.
> 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
> TSQL2 spec ftp://ftp.cs.arizona.edu/tsql/tsql2/spec.pdf
> Temporal Data and the Relational Model - Date et al
> Dozens of publications
> Scott Bailey
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
In response to
pgsql-hackers by date
|Next:||From: Pavel Stehule||Date: 2009-10-29 08:48:46|
|Subject: Grouping sets|
|Previous:||From: Peter Eisentraut||Date: 2009-10-29 07:47:51|
|Subject: Re: [PATCHES] updated hash functions for postgresql v1|