Re: BUG #4963: Selecting timestamp without timezone at timezone gives wrong output

"William Crawford" <william@ezyield.com> wrote:

> set time zone 'US/Eastern';
> select
>     timestamp '2009-01-01',
>     timestamp '2009-01-01' at time zone 'US/Pacific'
>                            as withouttimezone,
>     timestamp with time zone '2009-01-01' at time zone 'US/Pacific'
>                            as withtimezone;
>
>       timestamp      |    withouttimezone     |    withtimezone
> ---------------------+------------------------+---------------------
>  2009-01-01 00:00:00 | 2009-01-01 03:00:00-05 | 2008-12-31 21:00:00
> (1 row)
>
> I expect the last 2 values to be the same.

If you tilt your head just right, these make sense.

The withouttimezone column sees "timestamp '2009-01-01'" and takes
that as a timestamp without time zone.  Since it has no association
with any time zone, it doesn't yet represent any moment in time.  Then
you say you want to associate that abstract notion with the Pacific
time zone, so it does, and it becomes a timestamp with time zone
reflecting '2009-01-01 00:00:00' in the Pacific time zone.  Then you
display it without specifying the time zone in which to view it, so it
shows it in your time zone, which is three hours later by your local
clock.

The withouttimezone column sees the literal in your local time and
calculates what the clock would say in the Pacific time zone at that
moment.

Timestamp without time zone is generally both useless and dangerous.

-Kevin