avoiding Y2K scenarios

smook

subject sounds crazy but...

I have a table of rates that I want to validate WHEN the rate is good for. Example:

rate_table

rate_id
rate
valid_from
valid_to

bookings

booker
date

this is simple so far - select booker,date,amount from rate_table,bookings where date between valid_from and valid_to etc.....

However, the client wants open ended "valid_to" ... its valid until they say it changes. My first solution is if they DON'T define a valid_to then set it at a REALLY BIG date ... like Dec 31, 2030 ...

If I do this, am I setting myself up for a big Jan 1, 2031 problem??? (obviously) ... what do i do instead?

michael

Firstly, use epic dates.

Such as 2002-10-21 ...

Timestamp similar.

There's an RFC on it, but I'm not looking it up.

If they want an 'indefinate future date', select 3000-01-01.

By that time you're software product and server will ahve collected dust and the people who hired you to programme it will be [900 years] long dead. And I don't think it takes up that many more bits, but it's worth it for "indefinate future".

smook

the dates were just an example - I leave the formatting for the database which will be timestamps...

i just thought i could be more creative than a REALLY big date.

for example if the valid_to was simply 0 or some flag (like valid_from and to are equal). I could then write my sql as:

select from bookings,rates_table where (date is > valid_from and valid_to=FLAG*) OR (date is between valid_from and valid_to)

advantage being ... I get 901 years use of the query. 🙂

Problem - what is a valid flag for "open_ended" ... if I make them equal - what happens if someone really DOES start and end a rate on the same day. ??? Maybe a valid flag is just 3000-01-01 .. in which case I'm no better off with scenario 2 than scenario 1.

??? thoughts?