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] -----------------------------------------------------------------------------
