Feedback-ID: id3f144f1:Fastmail
User-Agent: Cyrus-JMAP/3.7.0-alpha0-811-gb808317eab-fm-20220801.001-gb808317e
Mime-Version: 1.0
Message-Id: <d591e640-7546-401a-8bfe-0d0e848a7c5f@www.fastmail.com>
Date: Tue, 09 Aug 2022 17:31:23 -0400
From: "John Ericson" <list@johnericson.me>
To: pgsql-sql <pgsql-sql@lists.postgresql.org>
Subject: Alternative to "AT TIME ZONE" that is less of a foot-gun?
Content-Type: multipart/alternative;
 boundary=93ba17433ceb46c898faa48e7ae9b83a
Archived-At: 
 <https://www.postgresql.org/message-id/d591e640-7546-401a-8bfe-0d0e848a7c5f%40www.fastmail.com>
Precedence: bulk

--93ba17433ceb46c898faa48e7ae9b83a
Content-Type: text/plain

According to the docs <https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT>, the semantics of "`AT TIME ZONE" on timestamps is convert back and forth between their "with timezone" and "without timezone" variants.`

This "toggling" behavior caught us by surprise. https://www.postgresql.org/message-id/2f2cb1690604200631l47c2a5b4xabe92bb6d22b4f2c@mail.gmail.com is an email from years ago with someone else having a similar confusion.

As I see it, the issue is in most programming languages --- informal English, "as" would seem to imply an idempotent conversion operation, not an inverse operation! As the reply to that email says, "It's really two distinct operations depending on whether the input is a timestamp with or without timezone." That avoids a foot-gun of confusing "toggling" behavior.

`I understand that `the semantics of "AT TIME ZONE" are fixed `per the SQL spec`, and so we are stuck with them. But on the model of ``timezone`(*`zone`*, *`timestamp`*) which `PostgreSQL also supports, might we add two new functions which only do half the semantics for safer programming? They could be something like this:
 * ``with_timezone(*zone, timestamp*``* without time zone*) returns *timestamp with time zone*
 * ``without_timezone(*zone, timestamp*``* with time zone*) returns *timestamp without time zone*
where giving them arguments with of the wrong type (e.g. the wrong with-timezone-ness) is simply and error.

SQL has lots of accumulated warts, and this hardly scratches the surface of what would be needed to make it truely "idiot proof", but it strikes me a simple, easy to implement new feature with clear benefits.

Curious what you all think,

John
--93ba17433ceb46c898faa48e7ae9b83a
Content-Type: text/html
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE html><html><head><title></title><style type=3D"text/css">p.Mso=
Normal,p.MsoNoSpacing{margin:0}</style></head><body><div>According to th=
e <a href=3D"https://www.postgresql.org/docs/current/functions-datetime.=
html#FUNCTIONS-DATETIME-ZONECONVERT">docs</a>, the semantics of "<code c=
lass=3D"literal">AT TIME ZONE" on timestamps is convert back and forth b=
etween their "with timezone" and "without timezone" variants.</code><br>=
</div><div><br></div><div>This "toggling" behavior caught us by surprise=
. <a href=3D"https://www.postgresql.org/message-id/2f2cb1690604200631l47=
c2a5b4xabe92bb6d22b4f2c@mail.gmail.com">https://www.postgresql.org/messa=
ge-id/2f2cb1690604200631l47c2a5b4xabe92bb6d22b4f2c@mail.gmail.com</a> is=
 an email from years ago with someone else having a similar confusion.<b=
r></div><div><br></div><div>As I see it, the issue is in most programmin=
g languages --- informal English, "as" would seem to imply an idempotent=
 conversion operation, not an inverse operation! As the reply to that em=
ail says, "It's really two distinct operations depending on whether the =
input is a timestamp with or without timezone." That avoids a foot-gun o=
f confusing "toggling" behavior.<br></div><div><br></div><div><code clas=
s=3D"literal">I understand that </code>the semantics of "AT TIME ZONE" a=
re fixed <code class=3D"literal">per the SQL spec</code>, and so we are =
stuck with them. But on the model of <code class=3D"literal"><code class=
=3D"function"><span class=3D"font" style=3D"font-family:menlo, consolas,=
 monospace, sans-serif;">timezone</span></code><span class=3D"font" styl=
e=3D"font-family:menlo, consolas, monospace, sans-serif;">(</span><i cla=
ss=3D"replaceable"><code><span class=3D"font" style=3D"font-family:menlo=
, consolas, monospace, sans-serif;">zone</span></code></i><span class=3D=
"font" style=3D"font-family:menlo, consolas, monospace, sans-serif;">, <=
/span><i class=3D"replaceable"><code><span class=3D"font" style=3D"font-=
family:menlo, consolas, monospace, sans-serif;">timestamp</span></code><=
/i><span class=3D"font" style=3D"font-family:menlo, consolas, monospace,=
 sans-serif;">)</span> which </code>PostgreSQL also supports, might we a=
dd two new functions which only do half the semantics for safer programm=
ing? They could be something like this:<br></div><ul><li><code class=3D"=
literal"><code class=3D"function">with_timezone(<i>zone, timestamp</i></=
code></code><i> without time zone</i>) returns <i>timestamp with time zo=
ne</i><br></li><li><code class=3D"literal"><code class=3D"function">with=
out_timezone(<i>zone, timestamp</i></code></code><i> with time zone</i>)=
 returns <i>timestamp without time zone</i></li></ul><div>where giving t=
hem arguments with of the wrong type (e.g. the wrong with-timezone-ness)=
 is simply and error.<br></div><div><br></div><div>SQL has lots of accum=
ulated warts, and this hardly scratches the surface of what would be nee=
ded to make it truely "idiot proof", but it strikes me a simple, easy to=
 implement new feature with clear benefits.<br></div><div><br></div><div=
>Curious what you all think,<br></div><div><br></div><div>John<br></div>=
</body></html>
--93ba17433ceb46c898faa48e7ae9b83a--