PGparam timestamp question

From: Andrew Chernow <ac(at)esilo(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Cc: Merlin Moncure <mmoncure(at)gmail(dot)com>
Subject: PGparam timestamp question
Date: 2007-12-09 14:44:04
Message-ID: 475BFF34.3010608@esilo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-patches

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

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Bruce Momjian 2007-12-09 15:18:54 Re: Release Note Changes
Previous Message Mark Cave-Ayland 2007-12-09 14:32:29 Re: Possible PostgreSQL 8.3beta4 bug with MD5 authentication in psql?

Browse pgsql-patches by date

  From Date Subject
Next Message Robert Treat 2007-12-09 16:26:43 Re: PGparam timestamp question
Previous Message Merlin Moncure 2007-12-09 02:27:04 Re: [PATCH] automatic integer conversion