I have a requirement to store dates and durations arising from multiple different calendars. In particular I need to store dates that:
- Span the change to Gregorian calendars in different countries at different times
- Cover a historic period of at least 500 years
- Deal with multiple types of calendar - lunar, solar, Chinese, Financial, Christian, UTC, Muslim.
- Deal with the change, in the UK, of the year end from 31st March to 31st December, and comparable changes in other countries.
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:
- A data structure to store dates and durations that meet the above requirements OR
- A reference to such a data structure OR
- Offer guidelines to approach the structuring of such storage OR
- Any points that may help me to a solution.
EDIT:
@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.
EDIT:
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.