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.
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? |