Mark Klein writes:
...
>We ended up keeping out dates in a normalized binary fashion. We chose
>some base date that would be earlier than our earliest possible date as
>the base. The dates were stored internally as the number of days from
>this base date. We developed conversion routines to convert between the
>internal and external date formats. These routines were as fast as they
>could possibly be (in fact, they were entirely algebraic - they didn't
>use tranditional table lookups to calculate and convert dates, so there
>was no looping).
...
 
In some work I do with a part of DOD, we (still) keep many of our dates in
CALENDAR format.  The reason is simple.  CALENDAR takes two bytes - the
alternative format (YYYYMMDD) takes 8 bytes as a text field.  We move a lot
of records around the world over VERY poor lines.  A real thorn in my side
is a postage stamp island in the middle of an ocean.  The datacomm to the
island is so poor that we had to use store-and-forward to move data going to
and from the island.  We have a 9.6 line connecting to the island.  When the
circuit is up (sometimes as much as an hour a day) we get throughput rates
approaching 30 CPS! (we are one user of a very heavily multiplexed link).
If you manage to DS to the island's 3000, you typically get a 30+ second
delay between the time you type SHOWTIME and the time you get a response.  A
SHOWME usually appears in at least two blocks, with about 20-30 seconds
between the blocks.  With average throughputs of 13-27 characters per
second, keeping the date in CALENDAR format and saving a few bytes makes a
big difference in how many transactions can be passed over the line.
 
Now a new date format has appeared that has growing support among the users
and may become our date field of the near future - the binary date field
used by the PC based spreadsheet that the project as adopted.  If you are
not familiar with the date fields used by LOTUS 1-2-3, QUATTRO PRO, and
Excel, take a look.  The form and operation is very similar to what Mark
Klein described in his post - a value which describes an offset from a base
date.  The advantage our users see in standardizing on that format is that
with ODBC, their spreadsheet date format will be the same as our ImageSQL
format.  This proposed date format has not yet been adopted by the DOD
powers that be - yet (as in adopt first, research feasibility later).  Does
anyone know of an ImageSQL database that uses a binary date field compatible
with the date field of LOTUS 1-2-3, QUATTRO PRO, or Excel?  If so, what have
been your experiences?
 
John Korb