Hi,
I have a question regarding postgres 8.2 handling of timezones.
I receive posts of unix timestamps and convert them and save them to a
timestamptz(0). I've read the docs on this, timestamptz stores
internally as utc. The date is formatted per local time on display.
If ruby and python tell me the value I just inserted into the db,
1174773136, is Sat Mar 24 21:52:16 UTC 2007, then I expect that
set time zone 0; -- format as though I'm in utc
select measurement_time
from table
will also yield that time.
Instead it yields "2007-03-25 04:52:16+00", which means it interprets
the original value as local time (I'm PDT), and then formats it as UTC
by adding 7 hours to it.
If I set time zone -7, then I get "2007-03-24 21:52:16-07". (set time
zone 'PDT' returns unrecognized time zone.)
What am I misunderstanding and how do I get it to interpret the value as
utc and then not offset it when I view it, like python and ruby do?
Any help is appreciated,
Danny