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
|
Список | 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 по дате отправления: