Обсуждение: BUG #4963: Selecting timestamp without timezone at timezone gives wrong output

The following bug has been logged online:

Bug reference:      4963
Logged by:          William Crawford
Email address:      william@ezyield.com
PostgreSQL version: 8.3.7
Operating system:   Gentoo
Description:        Selecting timestamp without timezone at timezone gives
wrong output
Details:

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.  (WithTimeZone is correct.)
Instead, it goes the correct number of hours in the wrong direction for
WithoutTimeZone.  This happens for all time zones that I've tested.

Also happens when pulling data from a table, and not just on this select
statement, and with or without times.

select version();
                                                          version
----------------------------------------------------------------------------
-----------------------------------------------
 PostgreSQL 8.3.7 on x86_64-pc-linux-gnu, compiled by GCC
x86_64-pc-linux-gnu-gcc (Gentoo 4.3.2-r3 p1.6, pie-10.1.5) 4.3.2

(Also happens on 8.2.6.)

"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

"Kevin Grittner" <Kevin.Grittner@wicourts.gov> wrote:
> The withouttimezone column sees the literal in your local time and

s/withouttimezone/withtimezone/

-Kevin