Re: CLSQL Wall-Time - Timestamptz vs Timestamp issues
"Russ Tyndall" <[email protected]> Wed, 7 Feb 2018 10:06:57 -0500
| Newsgroups | gmane.lisp.clsql.general |
|---|---|
| Message-ID | <075A468AB8BB4D8EB41DF318D1B6080F.MAI@mailproc1.me.acceleration.net> |
This is a multi-part message in MIME format. --===============0584466503207602629== Content-Type: multipart/alternative; boundary="__=_AltPart_1348418846_1335802170" This is a multi-part message in MIME format. --__=_AltPart_1348418846_1335802170 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable > why would one ever put a non-utc time in a production database Regardless of purpose or theory, I still regularly work with poorly designed databases that I did not create. I don't want my tools to limit me to only working on "correct" databases. > What is the actual benefit=3F Fundamentally if I want to accurately represent the two different data types (timestamp, timestamptz), there either needs to be two separate datatypes, or I need to add a bit to the existing one that allows them to be distinguished. In an effort to preserve backward compatibility, I chose the bit (so all types remain the same) and to do conversions between the two formats for comparison leaving the types the same. As a direct example: what is the correct way, in common lisp, to compare the results of a timestamp queried from the database (has no zone information) and a timestamptz (is in UTC). You can say "no one should use timestamp", or "thats undefined", but without distinguishing between the two state (zoneless, and UTC) you cannot actually choose what happens or even say that it is undefined - you have lost the information that one came zoneless and one in UTC. Another example: a csv import has datum "2016-01-01 09:58:37" being copied to both a timestamp and a timestamptz field. (Say a data intern used COPY to put this directly into some new temp tables in the database). Now I have to do some calculations based on that, but upon querying this data from the timestamp field I get "2016-07-01 09:58:37" and from the timestamptz field I get "2016-07-01 11:58:37" with no LISP indicator that these are different timestamps. Hope this helps explain my reasoning, Cheers, Russ Tyndall Acceleration.net ----- Original Message ----- From: [email protected] [mailto:[email protected]] To: [email protected] Sent: Wed, 7 Feb 2018 11:27:11 +0000 Subject: Re: [CLSQL] CLSQL Wall-Time - Timestamptz vs Timestamp issues > On 2018-02-07, at 09:11, james anderson <[email protected]> wrote: > > good morning; > >> On 2018-02-07, at 02:40, Russ Tyndall <[email protected]> wrote: >> >>> Without this change, `timestamptz`s are read as localtimes and saved as= >>> localtimes, when they should be read and printed as UTC times - which l= eads >>> to those fields cursoring (incrementing by offset) because they are con= tinuously >>> reconverted to UTC from localtimes. >> >> The timestamp data type is a zoneless time (left to the application to d= etermine if its UTC >> or local time or whatever), the timestamptz datatype is a UTC time. If w= e don't track that >> some of the times are zonless/local and some are UTC, then it becomes im= possible to tell >> when one should convert to a UTC and when one shouldn't (generally leadi= ng 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 correc= tly account for the >> two different datatypes (zoneless vs UTC) and correctly print and read t= hose values, I have >> to track minimally a single bit differentiating the two. > > i understand your text, above, but, i reiterate my question: why would on= e ever put a non-utc time in a production database=3F perhaps my question is clearer if i rephrase it. is the purpose of the database to represent a model of external events or t= o represent a model of time=3F in the former case, does the particular value recorded for a time zone affe= ct the position of the record in the model domain or just its representa= tion=3F if just the latter, why is the representation to be carried into the persis= tent record=3F as this requires additional implementation effort for the persistence layer= , what is the actual benefit=3F best regards, from berlin, > > > > _______________________________________________ > CLSQL mailing list > CLSQL-2NDrxpH/[email protected] > http://lists.kpe.io/cgi-bin/mailman/listinfo/clsql _______________________________________________ CLSQL mailing list CLSQL-2NDrxpH/[email protected] http://lists.kpe.io/cgi-bin/mailman/listinfo/clsql --__=_AltPart_1348418846_1335802170 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable > why would one ever put a non-utc time in a production database<br/><br= />Regardless of purpose or theory, I still regularly work with poorly <b= r/>designed databases that I did not create. I don't want my tools to <= br/>limit me to only working on "correct" databases.<br/><br/>> What = is the actual benefit=3F<br/><br/>Fundamentally if I want to accurately = represent the two different<br/>data types (timestamp, timestamptz), the= re either needs to be two<br/>separate datatypes, or I need to add a bit= to the existing one that<br/>allows them to be distinguished. In an eff= ort to preserve<br/>backward compatibility, I chose the bit (so all type= s remain the<br/>same) and to do conversions between the two formats for= comparison<br/>leaving the types the same.<br/><br/>As a direct example= : what is the correct way, in common lisp, to<br/>compare the results of= a timestamp queried from the database (has no<br/>zone information) and= a timestamptz (is in UTC). You can say "no one<br/>should use timestam= p", or "thats undefined", but without<br/>distinguishing between the two= state (zoneless, and UTC) you cannot<br/>actually choose what happens o= r even say that it is undefined - you<br/>have lost the information that= one came zoneless and one in UTC.<br/><br/>Another example: a csv impor= t has datum "2016-01-01 09:58:37" being<br/>copied to both a timestamp a= nd a timestamptz field. (Say a data intern<br/>used COPY to put this dir= ectly into some new temp tables in the<br/>database). Now I have to do s= ome calculations based on that, but upon<br/>querying this data from the= timestamp field I get "2016-07-01 09:58:37"<br/>and from the timestampt= z field I get "2016-07-01 11:58:37" with no LISP<br/>indicator that thes= e are different timestamps. <br/><br/>Hope this helps explain my reason= ing,<br/>Cheers,<br/>Russ Tyndall<br/>Acceleration.net<br/><br/><br/>---= -- Original Message -----<br/>From: [email protected] [mailto:james@dydra.= com]<br/>To: [email protected]<br/>Sent: Wed, 7 Feb 2018 11:27:11 +0000<b= r/>Subject: Re: [CLSQL] CLSQL Wall-Time - Timestamptz vs Timestamp issue= s<br/><br/><br/>> On 2018-02-07, at 09:11, james anderson <james@d= ydra.com> wrote:<br/>> <br/>> good morning;<br/>> <br/>>&= gt; On 2018-02-07, at 02:40, Russ Tyndall <[email protected]> = wrote:<br/>>> <br/>>>> Without this change, `timestamptz`= s are read as localtimes and saved as <br/>>>> localtimes, when= they should be read and printed as UTC times - which leads<br/>>>= > to those fields cursoring (incrementing by offset) because they are= continuously<br/>>>> reconverted to UTC from localtimes.<br/>&= gt;> <br/>>> The timestamp data type is a zoneless time (left t= o the application to determine if its UTC <br/>>> or local time or= whatever), the timestamptz datatype is a UTC time. If we don't track th= at <br/>>> some of the times are zonless/local and some are UTC, t= hen it becomes impossible to tell<br/>>> when one should convert t= o 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 "times= tamp", so I believe to correctly account for the<br/>>> two differ= ent datatypes (zoneless vs UTC) and correctly print and read those value= s, I have<br/>>> to track minimally a single bit differentiating t= he two.<br/>> <br/>> i understand your text, above, but, i reitera= te my question: why would one ever put a non-utc time in a production da= tabase=3F<br/><br/>perhaps my question is clearer if i rephrase it.<br/>= <br/>is the purpose of the database to represent a model of external eve= nts or to represent a model of time=3F<br/>in the former case, does the = particular value recorded for a time zone affect the position of the rec= ord in the model domain or just its representation=3F<br/>if just the la= tter, why is the representation to be carried into the persistent record= =3F<br/>as this requires additional implementation effort for the persis= tence layer, what is the actual benefit=3F<br/><br/>best regards, from b= erlin,<br/><br/>> <br/>> <br/>> <br/>> _____________________= __________________________<br/>> CLSQL mailing list<br/>> CLSQL@li= sts.kpe.io<br/>> http://lists.kpe.io/cgi-bin/mailman/listinfo/clsql<b= r/><br/><br/>_______________________________________________<br/>CLSQL m= ailing list<br/>CLSQL-2NDrxpH/[email protected]<br/>http://lists.kpe.io/cgi-bin/mailm= an/listinfo/clsql<br/><br/> --__=_AltPart_1348418846_1335802170-- --===============0584466503207602629== Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: base64 Content-Disposition: inline X19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX18KQ0xTUUwgbWFp bGluZyBsaXN0CkNMU1FMQGxpc3RzLmtwZS5pbwpodHRwOi8vbGlzdHMua3BlLmlvL2NnaS1iaW4v bWFpbG1hbi9saXN0aW5mby9jbHNxbAo= --===============0584466503207602629==--