X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [64.117.224.130])
	by svr1.postgresql.org (Postfix) with ESMTP id A3C22D1F44D
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 14 Aug 2003 05:13:46 +0000 (GMT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new,
	port 10024) with ESMTP id 89234-09
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 14 Aug 2003 02:12:08 -0300 (ADT)
Received: from titan.wwc.edu (unknown [66.62.165.251])
	by svr1.postgresql.org (Postfix) with ESMTP id E5B98D1F6EE
	for <pgsql-sql@postgresql.org>; Thu, 14 Aug 2003 01:58:14 -0300 (ADT)
Received: from wwc.edu (scotma-w2.wwc.edu [172.17.66.3])
	by titan.wwc.edu (Postfix) with ESMTP id A0B0210410
	for <pgsql-sql@postgresql.org>; Wed, 13 Aug 2003 21:57:37 -0700 (PDT)
Message-ID: <3F3B171E.3010402@wwc.edu>
Date: Wed, 13 Aug 2003 21:59:10 -0700
From: Jesse Scott <scotje@wwc.edu>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US;
	rv:1.5a) Gecko/20030625 Thunderbird/0.1a
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: Postgresql SQL Mailing List <pgsql-sql@postgresql.org>
Subject: Re: Timezone troubles
References: <3F3B001B.8060506@wwc.edu> <200308132112.44171.josh@agliodbs.com>
	<3F3B124D.6080606@wwc.edu>
In-Reply-To: <3F3B124D.6080606@wwc.edu>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200308/170
X-Sequence-Number: 14786

Well it seems the problem is that PG is returning the time in UTC, PHP 
isn't touching it.  The following query returns the disired results:

SELECT uid,
                        username,
                        pw_hash,
                        email,
                        theme,
                        EXTRACT(EPOCH FROM lastlogin AT TIME ZONE 'PDT') 
AS lastlogin,
                        EXTRACT(EPOCH FROM firstlogin AT TIME ZONE 
'PDT') AS firstlogin,
                        enabled
                        FROM users

Since my server time is in PDT and Postgres seems to know that I'm in 
PDT, I'm not sure exactly why it's giving me timestamps in UTC.  I 
actually dropped a whole table and made sure to redeclare it using 
"timestamp without time zone" and the behaviour is the same.  Maybe I 
just didn't understand what the expected behaviour was.

Anyway, thanks again!

-Jesse

Jesse Scott wrote:

> Ok, I was actually dyslexic when I read the version number, I have 
> 7.3.2 not 7.2.3, I'll investigate the timezone status though.
>
> Thanks,
>
> -Jesse
>
> Josh Berkus wrote:
>
>> Jesse,
>>
>>  
>>
>>> Here is the data definition and the query (version is 7.2.3 btw):
>>>   
>>
>> <snip>
>>  
>>
>>>  lastlogin timestamp DEFAULT 'now',
>>>  firstlogin timestamp,
>>>   
>>
>>
>> In 7.2.x, unless you declare a timestamp as "TIMESTAMP WITHOUT TIME 
>> ZONE", it is automatically created as *with* time zone.   I think 
>> this may be your problem.
>>
>>  
>>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if 
> your
>      joining column's datatypes do not match
>