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