How to check date-interval constraints - Mailing list pgsql-sql

From Andreas
Subject How to check date-interval constraints
Date
Msg-id 4407B7C1.7030101@gmx.net
Whole thread Raw
Responses Re: How to check date-interval constraints
List pgsql-sql
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.



pgsql-sql by date:

Previous
From: "Simon Kinsella"
Date:
Subject: Re: Help with trigger that updates a row prior to a potentially aborted deletion?
Next
From: Michael Fuhr
Date:
Subject: Re: How to check date-interval constraints