Re: PGparam timestamp question

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.
>