I am trying to add support for timestamps in our proposed libpq PGparam patch.
I ran into something I don't really understand. I wasn't sure if it was my
libpq code that was wrong (converts a binary timestamp into a time_t or struct
tm) so I tried it from psql.
Server is using EST (8.3devel) x86_64 centos 5
TIMESTAMP WITHOUT TIME ZONE appears to be incorrectly adjusting the time.
postgres=# create table t (ts timestamp);
postgres=# insert into t values (now());
postgres=# select * from t; ts
---------------------------- 2007-12-09 08:00:00.056244
postgres=# select ts at time zone 'UTC' from t; timezone
------------------------------- 2007-12-09 03:00:00.056244-05 ====> Shouldn't this be 13:00
TIMESTAMP WITH TIME ZONE returns the result I would expect.
postgres=# create table t (ts timestamp with time zone);
postgres=# insert into t values (now());
postgres=# select * from t; ts
---------------------------- 2007-12-09 08:00:00.056244
postgres=# select ts at time zone 'UTC' from t; timezone
------------------------------- 2007-12-09 13:00:00.056244-05
Is this expected/desired behavior? If it is, how are timestamps stored
internally for WITHOUT TIME ZONE types? The docs don't really say. They do
discuss how WITH TIME ZONE stores things, but WITHOUT is a little blury. Maybe
I am missing something simple.
Andrew
