Re: BUG #4963: Selecting timestamp without timezone at timezone gives wrong output - Mailing list pgsql-bugs

From Kevin Grittner
Subject Re: BUG #4963: Selecting timestamp without timezone at timezone gives wrong output
Date
Msg-id 4A78408E020000250002931A@gw.wicourts.gov
Whole thread Raw
In response to BUG #4963: Selecting timestamp without timezone at timezone gives wrong output  ("William Crawford" <william@ezyield.com>)
Responses Re: BUG #4963: Selecting timestamp without timezone at timezone gives wrong output
List pgsql-bugs
"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

pgsql-bugs by date:

Previous
From: "William Crawford"
Date:
Subject: BUG #4963: Selecting timestamp without timezone at timezone gives wrong output
Next
From: "Kevin Grittner"
Date:
Subject: Re: BUG #4963: Selecting timestamp without timezone at timezone gives wrong output