Re: Timezone troubles

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
>