Okay, thanks. So using WITHOUT TIME ZONE basically means, store the provided
value as UTC. Meaning, 8AM EST NOW() is stored/treated as 8AM UTC.
That explains why my libpq code was getting 3AM for "without time zone" values.
I am using code from src/interfaces/ecpg/pgtypeslib/timestamp.c
timestamp2tm(). That uses localtime() after converting the timestamp to an
epoch value. I changed this code so that it calls gmtime() for TIMESTAMPOID and
localtime() for TIMESTAMPTZOID. Now it works perfectly :)
Need to figure out how to handle times outside of the system time_t range.
Thanks again,
Andrew
Robert Treat wrote:
On Sunday 09 December 2007 09:44, Andrew Chernow wrote:
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
No. 8 AM UTC is 3 AM Eastern.
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
Correspondingly, 8 AM eastern is 1 PM UTC.
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.
When timestamptzs are converted to timestamp, there is no time adjust, you
simply lose the tz offset information:
pagila=# select now(), now()::timestamp;
-[ RECORD 1 ]----------------------
now | 2007-12-09 11:25:52.923612-05
now | 2007-12-09 11:25:52.923612
If you store without timezone, you lose the original timezone information, so
selecting out "with time zone" simply selects the stored time in the time
zone you selected. HTH.
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
