From: Jesse Scott <scotje@wwc.edu>
To: Postgresql SQL Mailing List <pgsql-sql@postgresql.org>
Subject: Re: Timezone troubles
Date: Wed, 13 Aug 2003 21:59:10 -0700
Message-ID: <3F3B171E.3010402@wwc.edu> (raw)
In-Reply-To: <3F3B124D.6080606@wwc.edu>
References: <3F3B001B.8060506@wwc.edu>
<200308132112.44171.josh@agliodbs.com>
<3F3B124D.6080606@wwc.edu>
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
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: scotje@wwc.edu
Subject: Re: Timezone troubles
In-Reply-To: <3F3B171E.3010402@wwc.edu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox