Re: PGparam timestamp question

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.

--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL