Message-ID: <4407B7C1.7030101@gmx.net>
Date: Fri, 03 Mar 2006 04:28:01 +0100
From: Andreas <maps.on@gmx.net>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; de-AT;
	rv:1.8.0.1) Gecko/20060130 SeaMonkey/1.0
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: How to check date-interval constraints
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hi,

I'd like to have a table that looks like this:

my_option (
  id               serial primary key,
  myvalue      double,
  valid_start   timestamp,
  valid_stop   timestamp
);

I want to store values that are only valid in a given 
start-stop-interval so I could find a date-specific  value  for NOW()  
or some other given date.
select myvalue from my_option where  somedate  between valid_start and 
valid_stop;

How can I have a constraint, that prohibits nesting or overlapping 
intervals?

1    7    2006-1-1     2006-1-31
2    9    2006-2-1     2006-2-28               OK
3    5    2006-1-10   2006-1-20               BAD  lies within line 1
4    3    2006-1-20   2006-2-10               BAD  starts within line 1 
and ends in line 2


To make it even more interesting, it'd be nice to add a type-column so I 
could ask:
select myvalue from my_option where now() between valid_start and 
valid_stop AND mytype=42;

Then interval should ONLY not overlap with other intervals of the SAME type.