Re: CLSQL Wall-Time - Timestamptz vs Timestamp issues

This is a multi-part message in MIME format.

--===============4375785233104110608==
Content-Type: multipart/alternative;
	boundary="__=_AltPart_493020362_678064745"

This is a multi-part message in MIME format.

--__=_AltPart_493020362_678064745
Content-Type: text/plain;
	charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

I feel like your question is disingenuous, and implies we are not all
mired by years of past poor decisions and applications we dont
control.  Some people *do* gleefully store whatever they want where
ever they want.  This includes numbers in text fields, dates without
timezones, and basically any other bad design you can imagine.  As a
consultant, I have had to deal with every possible bad design
decision, and I still need to interface with it and not cause bugs.  I
don't believe that it is the database interface layers job to be
political about what features of the backend it supports or which
applications should be able to make use of it.

Additionally I have problems with this particularly as no user enters
dates and times with a timezone - meaning I *must* accept that some
dates are local and some are UTC and it makes it much easier to
operate with if this is tracked.

Cheers,
Russ Tyndall
Acceleration.net



----- Original Message -----
From: james anderson [mailto:[email protected]]
To: [email protected]
Sent: Wed, 7 Feb 2018 08:11:51 +0000
Subject: Re: [CLSQL] CLSQL Wall-Time - Timestamptz vs Timestamp issues

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 le=
ads
>> to those fields cursoring (incrementing by offset) because they are cont=
inuously
>> reconverted to UTC from localtimes.
> 
> The timestamp data type is a zoneless time (left to the application to de=
termine 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 imp=
ossible to tell
> when one should convert to a UTC and when one shouldn't (generally leadin=
g 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 correct=
ly account for the
> two different datatypes (zoneless vs UTC) and correctly print and read th=
ose values, I have
> to track minimally a single bit differentiating the two.

i understand your text, above, but, i reiterate my question: why would one =
ever put a non-utc time in a production database=3F



_______________________________________________
CLSQL mailing list
CLSQL-2NDrxpH/[email protected]
http://lists.kpe.io/cgi-bin/mailman/listinfo/clsql


--__=_AltPart_493020362_678064745
Content-Type: text/html;
	charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

I feel like your question is disingenuous, and implies we are not all<br/>m=
ired by years of past poor decisions and applications we dont<br/>contro=
l.  Some people *do* gleefully store whatever they want where<br/>ever t=
hey want.  This includes numbers in text fields, dates without<br/>timez=
ones, and basically any other bad design you can imagine.  As a<br/>cons=
ultant, I have had to deal with every possible bad design<br/>decision, =
and I still need to interface with it and not cause bugs.  I<br/>don't b=
elieve that it is the database interface layers job to be<br/>political =
about what features of the backend it supports or which<br/>applications=
 should be able to make use of it.<br/><br/>Additionally I have problems=
 with this particularly as no user enters<br/>dates and times with a tim=
ezone - meaning I *must* accept that some<br/>dates are local and some a=
re UTC and it makes it much easier to<br/>operate with if this is tracke=
d.<br/><br/>Cheers,<br/>Russ Tyndall<br/>Acceleration.net<br/><br/><br/>=
<br/>----- Original Message -----<br/>From: james anderson [mailto:james=
@dydra.com]<br/>To: [email protected]<br/>Sent: Wed, 7 Feb 2018 08:11:51 =
+0000<br/>Subject: Re: [CLSQL] CLSQL Wall-Time - Timestamptz vs Timestam=
p issues<br/><br/>good morning;<br/><br/>&gt; On 2018-02-07, at 02:40, R=
uss Tyndall &lt;[email protected]&gt; wrote:<br/>&gt; <br/>&gt;&gt; =
Without this change, `timestamptz`s are read as localtimes and saved as =
<br/>&gt;&gt; localtimes, when they should be read and printed as UTC ti=
mes - which leads<br/>&gt;&gt; to those fields cursoring (incrementing b=
y offset) because they are continuously<br/>&gt;&gt; reconverted to UTC =
from localtimes.<br/>&gt; <br/>&gt; The timestamp data type is a zoneles=
s time (left to the application to determine if its UTC <br/>&gt; or loc=
al time or whatever), the timestamptz datatype is a UTC time. If we don'=
t track that <br/>&gt; some of the times are zonless/local and some are =
UTC, then it becomes impossible to tell<br/>&gt; when one should convert=
 to a UTC and when one shouldn't (generally leading to bugs <br/>&gt; re=
lating to converting not enough or too many times). Zoneless times are i=
n the SQL-92 <br/>&gt; and beyond standard for the datatype "timestamp",=
 so I believe to correctly account for the<br/>&gt; two different dataty=
pes (zoneless vs UTC) and correctly print and read those values, I have<=
br/>&gt; to track minimally a single bit differentiating the two.<br/><b=
r/>i understand your text, above, but, i reiterate my question: why woul=
d one ever put a non-utc time in a production database=3F<br/><br/><br/>=
<br/>_______________________________________________<br/>CLSQL mailing l=
ist<br/>CLSQL-2NDrxpH/[email protected]<br/>http://lists.kpe.io/cgi-bin/mailman/listi=
nfo/clsql<br/><br/>
--__=_AltPart_493020362_678064745--

--===============4375785233104110608==
Content-Type: text/plain; charset="utf-8"
MIME-Version: 1.0
Content-Transfer-Encoding: base64
Content-Disposition: inline

X19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX18KQ0xTUUwgbWFp
bGluZyBsaXN0CkNMU1FMQGxpc3RzLmtwZS5pbwpodHRwOi8vbGlzdHMua3BlLmlvL2NnaS1iaW4v
bWFpbG1hbi9saXN0aW5mby9jbHNxbAo=

--===============4375785233104110608==--