From: Andreas <maps.on@gmx.net>
To: pgsql-sql@postgresql.org
Subject: How to check date-interval constraints
Date: Fri, 03 Mar 2006 04:28:01 +0100
Message-ID: <4407B7C1.7030101@gmx.net> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: maps.on@gmx.net
Subject: Re: How to check date-interval constraints
In-Reply-To: <4407B7C1.7030101@gmx.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox