Re: Default to TIMESTAMP WITH TIME ZONE?

On Sat, 14 Aug 2021 at 09:03, Peter Eisentraut
<peter(dot)eisentraut(at)enterprisedb(dot)com> wrote:
>
> On 13.08.21 19:07, Tom Lane wrote:
> > "David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> >> On Fri, Aug 13, 2021 at 9:28 AM Simon Riggs <simon(dot)riggs(at)enterprisedb(dot)com>
> >> wrote:
> >>> The only hope is to eventually change the default, so probably
> >>> the best thing is to apply pressure via the SQL Std process.
> >
> >> Then there is no hope because this makes the situation worse.
> >
> > Agreed; the points I made upthread are just as valid if the change
> > is made in the standard. But I'd be astonished if the SQL committee
> > would consider such a change anyway.
>
> AFAIU, our timestamp with time zone type doesn't really do what the SQL
> standard specifies anyway, as it doesn't actually record the time zone,
> but it's more of a "timestamp with time zone aware formatting". For
> SQL, it might make sense to add a (third) time stamp type that behaves
> more like that.

Hmm, a new datatype would make sense, but I would go in the direction
of usability, since that's where this thread started.

It would also make sense to have a type called timestampUTC, where
1. all inputs that specify a timezone are converted to UTC
2. all inputs that do not specify a timezone are assumed to be UTC,
ignoring the setting of time zone
3. output is not affected by the session time zone, so wherever you
look at it from, you see UTC values

This allows the UTC Everywhere design pattern and also ensures that
all functions are immutable, so will not cause optimization problems.

This is useful because using TIMESTAMP WITHOUT TIME ZONE for UTC
Everywhere only works if nobody ever sets their time zone, which every
user can do.

--
Simon Riggs http://www.EnterpriseDB.com/