Re: Gregorian Calendar switch

Paul Brown wrote: > > Graham Crookham (graham.crookham2@sbs.siemens.co.uk) wrote: > : In the UK the switch to the Gregorian Calendar was in 1752 and the > : changeover was complex. Unfortunately Informix doesn't understand this > : at all and orders dates prior to this incorrectly as well as accepting > : dates that did not exist (Half of september ws actually missing). > : Is there any way of getting Informix to understand dates prior to 1752 > : as being part of the Julian Calendar as used in Britain with year ends > : in March. > > Absolutely! > > This kind of problem is precisely what Object-Relational DBMSs were > designed to solve. Realisticly, there isn't sufficient demand > for this kind of calender support to make it part of the server. > However, you can simply extend the ORDBMS with your own definitions of > 'date'. Here's how you do it. > > Start with an epoch. Might I suggest 7000 years before the year > 2000 AD. This was 7000 years before 2000. > > For giggles, let's assume there are 365.26 days in a year[1]. This > means that our epoch starts 2556820 days before 1st January 2000. > > Now for the tricky part. You need to write the functions to > convert date representations to integers and back again. It's > probably also a good idea to include, in your input and > ouput routines, some specification of the calender you want. > > i.e. '12-12-1748 G', '12-12-1748 J', '12-12-1748 R' etc. > > Thus: YourDateInput('1-1-2000 G') -> 2556820 > YourDateInput('31-12-1999 G') -> 2556819 > YourDateInput('1-1-1900 G') -> 252029 > > YourDateOutput(252030) -> '2-1-1900 G' > > etc. > > You plainly know more about this problem than anyone else, so the > specification of the algorithms shouldn't be too hard. If you can't > write the code then hire a nice consultant to write it for you. > (It feels like a fairly easy 2nd year computer science course > problem in 'C'). > > NOTE: For those still worried about the 'high end' strategy IFMX > is taking, this here is a 'high end' problem. > > [1] _Hammond_Internalional_Atlas_ Hammond Incorporated. 1993. There's an article by Michael Stonebraker in the latest issue of Byte about a very similar problem with "non-standard" dates. Not surprisingly, he suggests an object-relational database will provide the best solution. This seems to be another one of those problems that require ORDBMS. Just in case anyone is thinking the date problem is simple, consider the Islamic calendar, where I belive months are not always the same length. (Can anyone confirm my rusty memory?) SQL might have been different if it had been invented in the middle east... -- Peter Lancashire Information Systems Specialist, Bayer plc Eastern Way, Bury St Edmunds, Suffolk, IP32 7AH, UK Tel: +44-1635-562258, Fax: +44-1635-562281 Mail: Peter.Lancashire.PL1@bayer.co.uk --- My Internet plumbing does not allow me to mail and post news together. Sorry. All opinions are my own and not those of Bayer plc. --- Join Infuse, the UK Informix User Group at http://www.infuse.org.uk/