Re: PGparam timestamp question - Mailing list pgsql-hackers

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