Alternative to "AT TIME ZONE" that is less of a foot-gun?

From: "John Ericson" <list(at)johnericson(dot)me>
To: pgsql-sql <pgsql-sql(at)lists(dot)postgresql(dot)org>
Subject: Alternative to "AT TIME ZONE" that is less of a foot-gun?
Date: 2022-08-09 21:31:23
Message-ID: d591e640-7546-401a-8bfe-0d0e848a7c5f@www.fastmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Browse pgsql-sql by date

  From Date Subject
Next Message Zainik Theme 2022-08-18 08:46:45 #1 Best Open Source eCommerce Platform of 2022
Previous Message Tom Lane 2022-08-09 21:10:57 Re: Possible bug (or at least unexpected behavior)