Our business for many years was producing compilers and database
software to transport legacy software onto new platforms. We saw
literally thousands of custom application software implementations and
got to see the good, the bad and the ugly. As I said before dates were
a blind spot with most developers and they got themselves into much
quite avoidable trouble by jumping in without doing some research.
Those who implemented the classic date/time system with an epoch in
4712BC (from memory) did the best.
Lee Crain wrote:
John,
There are an almost infinite number of applications and operations that a
person can think of regarding the use of dates and times.
My employer's applications on that particular server were specific and
well defined. I'm not going to go into all the details of the requirement;
there were many.
I picked the best, low-tech solution that satisfied every existing
requirement and every foreseeable requirement. It was a very successful
implementation that solved all date-time related problems on that server.
And, (this is a very important point) if additional, unforeseen, date-time
functionality must be implemented in the future, like adding or
subtracting time intervals, those solutions can and should be implemented
in the server software, not the server database. In my solution, the
database is the repository of data, not logic.
Lee Crain
____________________________
-----Original Message-----
From: John Stanton [mailto:[EMAIL PROTECTED]
Sent: Monday, December 03, 2007 12:55 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Converting date from d/m/yy format
I didn't mean to nitpick, but my experience has been that date and time
processing is a blind spot. Over the years we have come across the most
unholy kludges and nasty errors as people try to process dates without
using the core theory. The Y2K situation was just one of the side
effects in an ongoing litany of difficulties.
Lee Crain wrote:
John,
None of the functionalities you mentioned were requirements on the
project
I worked on.
Clumsy? My approach simplified everything on that server and is in
production worldwide, and unmodified, today.
Lee Crain
_______________________
-----Original Message-----
From: John Stanton [mailto:[EMAIL PROTECTED]
Sent: Monday, December 03, 2007 12:18 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Converting date from d/m/yy format
That approach makes date processing clumsy. Distributing dates across
time zones and into different calendars is difficult. Date arithmetic
is awkward.
When you use dates and times based on the magic astronomical epoch
everything is simplified. Time zones and calendars are handled fairly
transparently.
Lee Crain wrote:
Several years ago when I worked for a Fortune 70 company, we had a
server
whose source code and database were complicated by timestamps. I say
complicated because there were different timestamp datatypes used for
different fields (inherited from the data sources), the data could be
stored in different formats, timestamp precision varied, and it was a
problem to translate back and forth between the different
representations
and compare them. All of this added up to the occasional exercise of
some
obscure bugs in the server.
I successfully undertook a project to fix this for all time. My
solution
was very simple: all timestamps were represented as strings in the
following format:
"YYYYMMDD:HHmmSS.nnnnnn"
This format, no what the original data source or format, became the
standard format for timestamps on this particular server. Precision was
to
the microsecond for all data, even if represented by zeroes.
This had several virtues:
When debugging software, all timestamps were readable when using
Debug.
Instead of looking at some binary number, the timestamp was easily
human
readable.
When using administrative tools to access the database, it was easy to
examine, modify, and compare timestamps, since they were all human
readable and in exactly the same format.
When comparing timestamps in the software to determine the most
current,
a simple string comparison always produced the correct result.
The only feature that might have been needed (but wasn't on this
particular server) was the ability to add or subtract time intervals
from
the timestamp.
You may wish to consider a similar approach to managing your date and
time
information.
Lee Crain
--------------------------------------------------------------------------
---
To unsubscribe, send email to [EMAIL PROTECTED]
--------------------------------------------------------------------------
---
--------------------------------------------------------------------------
---
To unsubscribe, send email to [EMAIL PROTECTED]
--------------------------------------------------------------------------
---
--------------------------------------------------------------------------
---
To unsubscribe, send email to [EMAIL PROTECTED]
--------------------------------------------------------------------------
---
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
