Very Computer

1. Unique Constraint Based on Date Range

I'm looking to apply a unique constraint to a table such that field A
must be unique based on the date range from Field B to Field C.

This is for a rate based service model whereby, for example, $5 is worth
1 hour of Internet access.   But next week, because of increased
competition, $5 is worth 2 hours of Internet access.  I want to maintain
a history what $5 bought during a specific period of time.

create table rates (
    effective_date AS timestamp,
    expiry_date AS timestamp,
    cost AS numeric (12,2),
     access_time AS integer  (in minutes)
);

So for a given cost, there may not be a record where the effective date
of one record overlaps the expiry date of another (and vice versa).

Example record set (effective date, expiry date, cost, access_time):

2003-01-01 | 2003-01-15 | 5.00 | 60
2003-01-15 |  infinity | 5.00 | 120
2003-01-01 | infinity | 1.00 | 10

An attempt to insert another 5.00 rate effective now would fail, because
a 5.00 rate exists that doesn't expire (i.e. the expiry date would have
to be updated to the effective date of the new record minus 1 second).

I can enforce this from the front end, but a db constraint would be great.

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate

      message can get through to the mailing list cleanly

2. DRDOS and NDO98

3. query based on result of another query

4. UPSs and HP-UX

5. query syntax in new mysql query browser

6. Looks Bad for DEC Suit

7. query date ranges

8. what's next? (56 or ISDN)

9. Help with Date Range Query

10. strange behavior with query optimizer regarding 'range'

11. query date ranges - step 2

12. Repost [Fwd: Query Range]

13. Dirt Slow Query On Datetime Range