How to check date-interval constraints

From: Andreas <maps(dot)on(at)gmx(dot)net>
To: pgsql-sql(at)postgresql(dot)org
Subject: How to check date-interval constraints
Date: 2006-03-03 03:28:01
Message-ID: 4407B7C1.7030101@gmx.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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.

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Michael Fuhr 2006-03-03 05:13:30 Re: How to check date-interval constraints
Previous Message Simon Kinsella 2006-03-03 00:19:22 Re: Help with trigger that updates a row prior to a potentially aborted deletion?