Thread: BUG #4963: Selecting timestamp without timezone at timezone gives wrong output
BUG #4963: Selecting timestamp without timezone at timezone gives wrong output
From
"William Crawford"
Date:
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.)
Re: BUG #4963: Selecting timestamp without timezone at timezone gives wrong output
From
"Kevin Grittner"
Date:
"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
Re: BUG #4963: Selecting timestamp without timezone at timezone gives wrong output
From
"Kevin Grittner"
Date:
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> wrote: > The withouttimezone column sees the literal in your local time and s/withouttimezone/withtimezone/ -Kevin