Re: iso-timestring fix / proposal for saner timezones

On Tue, 2 Feb 2010, Nathan Bird wrote:

> Date: Tue, 02 Feb 2010 16:11:51 -0500
> From: Nathan Bird <[email protected]>
> To: [email protected]
> Subject: [CLSQL] 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.

Thank you for the iso-timestring format fix, and the test of the
material re: timezones sounds great!

Carlos