Ideal timestamp data

Firebird initially looks like an ideal base for handling dates and times, with a date element separated from a time one. The vast majority of material only requires a date with day accuracy, but while you can work with dates, these are restricted to a Gregorian calendar. Adding Julian calendar dates is not supported, and the various holes created by countries switching calendars are not supported in date comparisons. So the use of generic DATE and TIMESTAMP has to be viewed against what data is being stored. Most databases have similar restrictions their date related functions. Even TIME has problems at times when one needs to handle negative times such as time zone offsets. The use of 64 bit Unix epoch times are the fallback n many cases, but even that is not an ideal soultion.

As a base there are a few rules that are difficult to get wrong. At least until time travel is actually achieved. So days follow one another, and similarly with events during the day. The position of the sun at mid day is nominally when it is directly over head, but given the variable nature of all of the movements that have created this basic framework, we work with 'mean time'. Sunrise and sunset can be used as other fixed points, that is where they actually occur all year round, but that indicates a reason that both the latitude and longitude are important in the base framework. The fact that astronomical observations are key to laying down the framework, it makes sense to use their base as a starting point, but even here no one can agree on one standard!

While normal time management uses midnight as the epoch, astronomers prefer a midday one since they tend to work over night. Since all genealogical data relies on the midnight version, this is something of a given for my data, so all that is required is to decide on a suitable starting point for day zero. Allowing for a 32 bit day count is still overkill, but avoiding merging the seconds count nicely also avoids the problems of leap seconds. Keeping date and time in separate fields even if handled combined as 'timestamp' makes perfect sense. Adoption the Julian Day Epoch of Jan 1, 4713 BC may be a little overkill, but at least it results in positive numbers for most of the currently available pre-Christian history and as far as I am aware, a conversion to all the common calendar systems is available.

As indicated, avoidng the leap second hole means that it only requires time to cover a single day. Something that the Firebird TIME value does nicely, but which does not cater for negative times. So we end up really needing to have a signed floating point number for the time element, with a count of seconds. Fractions of a day is easy, and the only fiddle is a list of leap second day flags. But this verion of TIME can be used for both clock, and the offsets for both LMT and Time Zone rules.

Extra fields to follow ... Calendar, Time Zone, Location and Versioning

Extra Material

ISO8601 - Data elements and interchange formats – Information interchange – Representation of dates and times

RFC3339 - Date and Time on the Internet: Timestamps

RFC7808 - Time Zone Data Distribution Service

RFC5545 - Internet Calendaring and Scheduling Core Object Specification (iCalendar)