The native VAX date was (is) 8 bytes. When 3rd party software suppliers started using the VAX as a commercial computer (which took DEC by surprise !) some had their own internal form of date that could be stored as two-byte signed integer based on the VAX base date + X. SYSTIME did this with their SYSTEL Transaction processing system because in the late 70's and 80's disks were still expensive. You needed very deep pockets to buy a VAX 11/780 with more than 4 megabytes of RAM too.
Defining dates in COBOL as PIC 9(6) was a pretty standard way of doing it back then. With insurance systems holding dozens of dates in a typical policy, those extra 2 bytes to hold the century add up to a great deal of wasted storage when you have databases of millions of records.
You need to allow for Leap Seconds if using UTC-referenced time. You need to allow for Summer Time where applicable if using civil dates. You need to allow also for Time Zone if more than one applies. You need to consider changes, past and future, in the Rules.
Consider storing Local Civil Time in ISO8601 *with* offset from GMT, **plus** UTC.
If you store it as seconds since some epoch, typically the unix epoch, since that's what everyones uses, you don't have to consider any of these. You should know what the offset is, and then just get your framework, whatever you're using, to do all the hard conversion work.
AISB, you should separate information storage from information presentation. That way it works in any timezone and future zone rules changes have no impact.
2038...
Andy
Snort - 64-bit integer. I'll see your 2038 and raise you 190 million years.
Not much use if you need a key comprising date (descending) followed by an ascii string like a name etc, which is a common requirement in the commercial world.
First normal form?
Time query joins are often a little tricky. I would normally expect to use a seperate field with a proper date time type.
As a surrogate key, I normally see people use auto increment.
Maybe that was just the places I worked.
For UNIX, GMT (or UTC if Leap Seconds are implemented). Note that the UNIX zero of time was 1 a.m. BST in the UK.
The VAX date is MJD 0 and CMJD 0, as I recall. If they use MJD, it should be that GMT date; if CMJD, it should be that date of the machine's location; but, DEC being US, it might be a Massachusetts date.
Note that the MSB of a DOS internal date, which sometimes looks like a sign bit, changes at the beginning of local 2044 - less than 23.5 years ahead.
FYI, a pending millennium bug in a draft RFC, possibly RFC3339, was averted in the late 1990s.
The draft RFC gave C code for Zeller's Congruence or similar, maybe in a Helpful Footnote. I do not doubt that, for several years, the code had been working perfectly for current dates. But I perceived that, for various dates (I forget details) in Jan/Feb of 2000-2019 or thereabouts, that code would do a 'mod 7' operation on a negative number, which does not give the desired effect. So I E-mailed the author, suggesting changing a -2 to +5 and explaining why - and soon received due thanks in reply.
I bet at your school, you were the one who could tell anyone the day of the week they were born on given their date of birth :-)
A bit more than that...
There are not; remember Summer Time changes, Leap Seconds, and locations which change their (standard) Time Zone. Sometimes, people and places cross the International Date Line, so a Day Count also needs care.
DEC RMS-11 Just regarded a composite key, comprising one or more data fields as a string of bytes, and used the ascii collating sequence to order them. In the case of a primary key, this was the physical order of the records in the file
Have something to add? Share your thoughts — no account required.
Ask the community — no account required