Edward M. Reingold, Nachum Dershowitz
This new edition of the successful calendars book is being published in the new millennium and expands the treatment of the previous edition to new calendar variants. Calendrical Calculations makes accurate calendrical algorithms readily available for computer use with LISP and Java code for all the algorithms included on CD, and updates available on the Web. It gives a description of fourteen calendars and how they relate to one another: the present civil calendar (Gregorian), the recent ISO commercial calendar, the old civil calendar (Julian), the Coptic and Ethiopic calendars, the Islamic (Moslem) calendar; the Baha'i, the Hebrew (Jewish) calendar, the Mayan calendars, the French Revolutionary calendar, the Chinese calendar, and both the old (mean) and new (true) Hindu (Indian) calendars. This new edition will be a valuable resource for working programmers, as well as a fount of useful algorithmic tools for computer scientists.
I have a requirement to store dates and durations arising from multiple different calendars. In particular I need to store dates that:
I also need to store durations which I have defined as the difference between two timestamps (date and time). This implies the need to be able to store a "zero" date - so I can store durations of, say, three and a half hours; or 10 minutes.
I have details of the computations needed. Firebird's timestamp is based on a date function that starts at January 1st, 100 CE, so is not capable of being used for durations in the way I need to record them. In addition this data type is geared up (like most timestamp functions) to record the number of days since a base date; it is not geared up to record calendar dates.
Could anyone suggest:
@Warren P has provided some excellent work in his responses. I obviously have not explained what I am seeking clearly enough, as his work concentrates on the computations and how to go about calculating these. All valuable and useful stuff, but not what I intended my question to convey.
I do have details of all the computations needed to convert between various representations of dates, and I have a fairly good idea of how to implement them (using elements such as Warren suggests). However, my requirement is to STORE dates which meet the various criteria listed above. Example: date to be stored - 'Third June 13 Charles II'. I am trying to determine an appropriate structure within which to store such dates.
I have amended my proposed schema. I have listed the attributes on each table, and defined the tables and attributes by examples, given in the third section of the entity box. I have used the example given in this question and answer in my definition by example, and have amended the example in my question to correspond. Although I have proved my schema by describing somebody else's example, this schema may still be over complicated; over analysed; miss some obvious simplification and may prove very difficult to implement (Indeed, it may be plain wrong). Any comments or suggestions would be most welcome.
If you are writing your own, as I assume you intend to, I would make a class that contains a TDateTime, and other fields, and I would base it on the functionality in the very nicely written mxDateTime extension for Python, which is very easily readable, open source, C code, that you could use to extract the gregorian calendar logic you are going to need.
Within certain limits, TDateTime is always right. It's epoch value (0) is December 30, 1899 at midnight. From there, you can calculate other julian day numbers. It supports negative values, and thus it will support more than 400 years. I believe you will start having to do corrections, at the time of the last Gregorian calendar reforms. If you go from Friday, 15 October 1582, and figure out its julian day number, and the reforms before and after that, you should be able to do all that you require. Be aware that the time of day runs "backwards" before 1899, but that this is purely a problem in human heads, the computer will be accurate, and will calculate the number of minutes and seconds, up to the limit of double precision floating point math for you. Stick with TDateTime as your base.
I found some really old BorlandPascal/TurboPascal code that handles a really wide range of dates here.
If you need to handle arabic, jewish, and other calendars, again, I refer you to Python as a great source of working examples. Not just the mxdatetime extension, but stuff like this.
For database persistence, you might want to base your date storage around julian day numbers, and your time as C-like seconds since midnight, if the maximum resolution you need is 1 second.
Here's a snippet I would start with, and do code completion on:
TCalendarDisplaySubtype = ( cdsGregorian,cdsHebrew,cdsArabic,cdsAztec, cdsValveSoftwareCompany, cdsWhoTheHeckKnows ); TDateInformation = class private FBaseDateTime:TDateTime; FYear,FMonth,FDay:Integer; // if -1 then not calculated yet. FCalendarDisplaySubtype:TCalendarDisplaySubtype; public function SetByDateInCE(Y,M,D,h,m,s:Integer):Boolean; function GetAsDateInCE(var Y,M,D,h,m,s:Integer):Boolean; function DisplayStr:String; function SetByDateInJewishCalendar( ... ); property BaseDateTime:TDateTime read FDateTime write FDateTime; property JulianDayNumber:Integer read GetJulianDayNumber write SetJulianDayNumber; property CalendarDisplaySubType:TCalendarDisplaySubtype; end;
I see no reason to STORE both the julian day number, and the TDateTime, just use a constant, subtract/add from the Trunc(FBaseDateTime) value, and return that, in the GetJulianDayNumber,SetJulianDayNumber functions. It might be worth having fields where you calculate the year, month, day, for the given calendar, once, and store them, making the display as string function much simpler and faster.
Update: It looks like you're better at ER Modelling than me, so if you posted that diagram, I'd upvote it, and that would be it. As for me, I'd be storing three fields; A Datetime field that is normalized to modern calendar standards, a text field (free form) containing the original scholarly date in whatever form, and a few other fields, that are subtype lookup table Foreign keys, to help me organize, and search on dates by the date and subtype. That would be IT for me.
Oracle supports six calendar systems. Following are the different calendars.
I wanted to know the number of months per a year in every calendar. Is there any sql command to get that information?
SQL - not as far as I know. My favorite reference for this sort of thing is Calendrical Calculations. There's also Calendrical Tabulations which provides 300 years (1900-2200) of day-by-day equivalences between many different calendars. Ed Reingold, one of the authors of the afore-mentioned books, does have a rather nifty little applet which allows you to view the current date in many different calendar systems (which of course begs the question, "Which one is truly the right calendar system?") - find it here. You can also buy the software used for the applet here, or can [contact the authors](mailto:firstname.lastname@example.org?subject=Calendar code use (free software)) if you want to use it in free software. A collection of Dr. Reingold's papers on calendars and related numerical stuff can be found here.
Party like it's 1999...