iso-timestring fix / proposal for saner timezones

This is one that we've had problems with and was also reported in the
survey.

iso-timestring used to output
      YYYY-MM-DD HH:MM:SS,,UUUUUU

This was an invalid format because there are the two commas and because
3 usec would be output as xx,,3 instead of xx.000003.  These invalid
timestamps were also unparseable by the
clsql:parse-timestring/parse-iso-8601-time functions.

It now outputs
      YYYY-MM-DD HH:MM:SS.UUUUUU
which should be a valid iso8601 timestamp and is parseable by
parse-timestring et al. Additionally, it does not print the ".UUUUUU"
part if there are no microseconds; useful for the databases that don't
support fractional seconds.

Patches that get us this far and a lot of tests to try and show it are
in the development branch.

The rest of this email documents a bug still present in clsql and offers
a proposal of how to fix it.
 * clsql:wall-time objects don't carry a timezone.
 * clsql-sys:parse-timestring:
  * with a timezone - shift by TZ into UTC so we don't have to store the
timezone information.
  * w/o a timezone (iso says it should be considered local) are parsed
directly into wall-time - no timezone shifting
 * Sending to databases
   * Many databases don't have any timezone info: ODBC, mysql,
sqlserver, some postgres datatypes.  We send and retrieve timezoneless.
This pretty much works.
   * databases' datatypes-with-a-timezone that receive a
timestring-without-a-timezone (what we currently output) read as local
in server's timezone; this is iso8601 behavior. When it is printed to
send back to CLSQL, the DB will add the timezone which clsql reads into
UTC resulting in a shift every roundtrip.  E.g. I send '2010-02-02' to
db, server reads, stores, and sends back (correctly) '2010-02-02
00:00:00-5' which CLSQL then parses as #<WALL-TIME: 2010-02-02
05:00:00.000000>. Through multiple trips loading and saving the data, it
will walk forward to the next day.


We propose adding some zone information to wall-times--or subclass
'iso-time'. It is a nullable slot that keeps track of what time zone
information was present when parsed. Parsing doesn't automatically shift
timezone to UTC. When printing a wall-time, if it has timezone info, we
print it in in the standard format, if it doesn't have timezone
information we print it as a local time which continues current behavior.

We add an explicit function to shift a wall-time's timezone to UTC,
updating the timezone field to keep track of that. All the math
functions would probably call that function before doing their own
operation to ease consistency of operations.

With this dates that are stored in fields with timezone data in the
database will retain that information and survive round trips. Entering
new dates from strings without timezone information will rely on setting
the db server or connection variable to the appropriate timezone. If you
are sticking those into a timezoneless field it should continue to work
as before.

-- 
Nathan Bird
[email protected]
http://www.acceleration.net/
Custom Programming, Design, Hosting, and Broadband.