Re: Default to TIMESTAMP WITH TIME ZONE?

On Thu, 12 Aug 2021 at 20:07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Bruce Momjian <bruce(at)momjian(dot)us> writes:
> > On Thu, Aug 12, 2021 at 07:24:58PM +0100, Simon Riggs wrote:
> >> I heard the moan about "Why doesn't TIMESTAMP mean TIMESTAMP WITH TIME
> >> ZONE" again today, so here is something concrete to address that.
> >>
> >> AFAIK, SQL Standard requires the default to be WITHOUT TIME ZONE, but
> >> nobody seems to think that is useful.
>
> Standards compliance is useful, no? If it isn't, there are about 1001
> other things nobody much likes about SQL.
>
> >> We even added a specially
> >> optimized ALTER TABLE command to make switching from WITHOUT to WITH
> >> TIME ZONE easy, so it is clearly an important thing to solve.
> >>
> >> So add a parameter called
> >> default_timestamp_with_timezone = off (default) | on
>
> > I thought we found that changing behavior via GUC usually ends badly.
>
> Yeah. Changing from SQL-spec to not-SQL-spec behavior is going to be
> one tough sell to begin with, even without the point that that's been
> our behavior for over two decades. But proposing to do it via a GUC
> is just not-even-worth-discussing territory. That would force every
> wannabe-portable program to cope with both behaviors; which would
> end up meaning that not only do you still have to take care to write
> WITH TIME ZONE when you want that, but *also* you'd have to be sure
> to write WITHOUT TIME ZONE when you want that. In short, the worst
> of both worlds.

All of which I agree with, but this wasn't a cute idea of mine, this
was what our users have requested because of the extreme annoyance
caused by the current behavior.

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