"William Crawford" <will...@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
-- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
