How to check date-interval constraints

Поиск
Список
Период
Сортировка
От Andreas
Тема How to check date-interval constraints
Дата
Msg-id 4407B7C1.7030101@gmx.net
обсуждение исходный текст
Ответы Re: How to check date-interval constraints  (Michael Fuhr <mike@fuhr.org>)
Список 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 по дате отправления:

Предыдущее
От: "Simon Kinsella"
Дата:
Сообщение: Re: Help with trigger that updates a row prior to a potentially aborted deletion?
Следующее
От: Michael Fuhr
Дата:
Сообщение: Re: How to check date-interval constraints