Re: CLSQL Wall-Time - Timestamptz vs Timestamp issues
"Russ Tyndall" <[email protected]> Tue, 6 Feb 2018 20:40:38 -0500
| Newsgroups | gmane.lisp.clsql.general |
|---|---|
| Message-ID | <14C0935AC6DF4A89B54EBC46C7111F03.MAI@mailproc1.me.acceleration.net> |
This is a multi-part message in MIME format. --===============8264816940776042376== Content-Type: multipart/alternative; boundary="__=_AltPart_2111964637_155652753" This is a multi-part message in MIME format. --__=_AltPart_2111964637_155652753 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable > Without this change, `timestamptz`s are read as localtimes and saved as > localtimes, when they should be read and printed as UTC times - which lea= ds > to those fields cursoring (incrementing by offset) because they are conti= nuously > reconverted to UTC from localtimes. The timestamp data type is a zoneless time (left to the application to dete= rmine if its UTC or local time or whatever), the timestamptz datatype is a UTC time. If we = don't track that some of the times are zonless/local and some are UTC, then it becomes impos= sible to tell when one should convert to a UTC and when one shouldn't (generally leading = to bugs relating to converting not enough or too many times). Zoneless times are in= the SQL-92 and beyond standard for the datatype "timestamp", so I believe to correctly= account for the two different datatypes (zoneless vs UTC) and correctly print and read tho= se values, I have to track minimally a single bit differentiating the two. Cheers, Russ Tyndall Acceleration.net ----- Original Message ----- From: James Anderson [mailto:[email protected]] To: [email protected] Sent: Tue, 6 Feb 2018 19:08:19 +0100 Subject: Re: [CLSQL] CLSQL Wall-Time - Timestamptz vs Timestamp issues why have you had to do this=3F is anyone storing anything other than utc temporal values in a production store=3F Am 06.02.2018 18:03 schrieb "Russ Tyndall" <[email protected]>: > Howdy CLSQL, > > I have created a patch to better distinguishing between zoneless > timestamps and UTC times, particularly as relates to > postgresql-socket3 backend. Without this change, `timestamptz`s are > read as localtimes and saved as localtimes, when they should be read > and printed as UTC times - which leads to those fields cursoring > (incrementing by offset) because they are continuously reconvert to > UTC from localtimes. I followed a minimal approach (following > postgresql' lead), of simply adding an is-utc=3F boolean to the > wall-time object. Previously zoned times were converted to UTC, but > since we didn't track that they were indistinguishable from unzoned > times after conversion. > > In order to preserver comparability between dates and times that are > local vs UTC, I have added a `time-to-utc` function that is called > on-the-fly during comparisons. I feel like this minimal approach gets > us 95% of the way toward a non-buggy timezone implementation. I doubt > that this covers *every* possible situation due to the complexity of > timezones. > > I verified the math using the local-time libary in a manually loaded > test file: tests/utc-time-compare. (Originally I had based the code > off of local-time, but then found how to do it using > decode-universal-time). > > I added two variables *default-timezone* and *default-timezone-is-dst=3F* > these are not exported and only used for testing currently, but could > be exported if there was a good external use case. > > https://github.com/UnwashedMeme/clsql/tree/timestamp-utc > > Kevin, I have also pushed this branch to your repo (at > timestamp-utc-no-localtime, timestamp-utc was a previous version using > local-time and I couldnt overwrite it). If you like, please feel free > to merge and increment, otherwise please comment and help me move > forward with better timezone support. Thanks for your work! > > > Cheers, > Russ Tyndall > Acceleration.net > > > _______________________________________________ > CLSQL mailing list > CLSQL-2NDrxpH/[email protected] > http://lists.kpe.io/cgi-bin/mailman/listinfo/clsql > > --__=_AltPart_2111964637_155652753 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable > Without this change, `timestamptz`s are read as localtimes and saved = as <br/>> localtimes, when they should be read and printed as UTC tim= es - which leads<br/>> to those fields cursoring (incrementing by off= set) because they are continuously<br/>> reconverted to UTC from loca= ltimes.<br/><br/>The timestamp data type is a zoneless time (left to the= application to determine if its UTC <br/>or local time or whatever), th= e timestamptz datatype is a UTC time. If we don't track that <br/>some = of the times are zonless/local and some are UTC, then it becomes impossi= ble to tell<br/>when one should convert to a UTC and when one shouldn't = (generally leading to bugs <br/>relating to converting not enough or too= many times). Zoneless times are in the SQL-92 <br/>and beyond standard = for the datatype "timestamp", so I believe to correctly account for the<= br/>two different datatypes (zoneless vs UTC) and correctly print and r= ead those values, I have<br/>to track minimally a single bit differenti= ating the two.<br/><br/>Cheers,<br/>Russ Tyndall<br/>Acceleration.net<br= /><br/><br/> <br/><br/>----- Original Message -----<br/>From: James Ande= rson [mailto:[email protected]]<br/>To: [email protected]= t<br/>Sent: Tue, 6 Feb 2018 19:08:19 +0100<br/>Subject: Re: [CLSQL] CLSQ= L Wall-Time - Timestamptz vs Timestamp issues<br/><br/>why have you had = to do this=3F<br/>is anyone storing anything other than utc temporal val= ues in a production<br/>store=3F<br/><br/>Am 06.02.2018 18:03 schrieb "R= uss Tyndall" <[email protected]>:<br/><br/>> Howdy CLSQL,<b= r/>><br/>> I have created a patch to better distinguishing between= zoneless<br/>> timestamps and UTC times, particularly as relates to<= br/>> postgresql-socket3 backend. Without this change, `timestamptz`s= are<br/>> read as localtimes and saved as localtimes, when they shou= ld be read<br/>> and printed as UTC times - which leads to those fiel= ds cursoring<br/>> (incrementing by offset) because they are continuo= usly reconvert to<br/>> UTC from localtimes. I followed a minimal app= roach (following<br/>> postgresql' lead), of simply adding an is-utc= =3F boolean to the<br/>> wall-time object. Previously zoned times = were converted to UTC, but<br/>> since we didn't track that they were= indistinguishable from unzoned<br/>> times after conversion.<br/>>= ;<br/>> In order to preserver comparability between dates and times t= hat are<br/>> local vs UTC, I have added a `time-to-utc` function tha= t is called<br/>> on-the-fly during comparisons. I feel like this min= imal approach gets<br/>> us 95% of the way toward a non-buggy timezon= e implementation. I doubt<br/>> that this covers *every* possible sit= uation due to the complexity of<br/>> timezones.<br/>><br/>> I = verified the math using the local-time libary in a manually loaded<br/>&= gt; test file: tests/utc-time-compare. (Originally I had based the code<= br/>> off of local-time, but then found how to do it using<br/>> d= ecode-universal-time).<br/>><br/>> I added two variables *default-= timezone* and *default-timezone-is-dst=3F*<br/>> these are not export= ed and only used for testing currently, but could<br/>> be exported i= f there was a good external use case.<br/>><br/>> https://github.c= om/UnwashedMeme/clsql/tree/timestamp-utc<br/>><br/>> Kevin, I have= also pushed this branch to your repo (at<br/>> timestamp-utc-no-loca= ltime, timestamp-utc was a previous version using<br/>> local-time an= d I couldnt overwrite it). If you like, please feel free<br/>> to mer= ge and increment, otherwise please comment and help me move<br/>> for= ward with better timezone support. Thanks for your work!<br/>><br/>&g= t;<br/>> Cheers,<br/>> Russ Tyndall<br/>> Acceleration.net<br/>= ><br/>><br/>> _______________________________________________<b= r/>> CLSQL mailing list<br/>> CLSQL-2NDrxpH/[email protected]<br/>> http://l= ists.kpe.io/cgi-bin/mailman/listinfo/clsql<br/>><br/>><br/><br/><b= r/> --__=_AltPart_2111964637_155652753-- --===============8264816940776042376== Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: base64 Content-Disposition: inline X19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX18KQ0xTUUwgbWFp bGluZyBsaXN0CkNMU1FMQGxpc3RzLmtwZS5pbwpodHRwOi8vbGlzdHMua3BlLmlvL2NnaS1iaW4v bWFpbG1hbi9saXN0aW5mby9jbHNxbAo= --===============8264816940776042376==--