Message-ID: <3F3B001B.8060506@wwc.edu>
Date: Wed, 13 Aug 2003 20:20:59 -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
MIME-Version: 1.0
To: Postgresql SQL Mailing List <pgsql-sql@postgresql.org>
Subject: Timezone troubles
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

I've got a timestamp (with no tz) field which I'm extracting to epoch 
format so that my PHP script can deal with it more easily.  
Unfortunately, somewhere along the line the timezone correction is 
happening twice, so the time I eventually get out is 7 hours earlier 
than it should be.  (For those of you keeping score at home, that would 
put me in the PDT timezone. :))

I read somewhere that PG always keeps timestamps in UTC and then 
converts to the local timezone when you select from the timestamp 
field.  If this is true, is there some reason that PHP would think that 
it needed to convert the timestamp again?  Is there a setting somewhere 
I need to tweak?  Should I just "SET TIME ZONE" to UTC before my query?

Here is the data definition and the query (version is 7.2.3 btw):

Schema:

CREATE TABLE public.users (
  uid int4 DEFAULT nextval('public.users_uid_seq'::text) NOT NULL,
  username varchar(64) NOT NULL,
  pw_hash char(32) NOT NULL,
  email varchar(128) NOT NULL,
  theme int4 NOT NULL,
  lastlogin timestamp DEFAULT 'now',
  firstlogin timestamp,
  enabled bool DEFAULT false
) WITH OIDS;


Query: SELECT uid,
                        username,
                        pw_hash,
                        email,
                        theme,
                        EXTRACT(EPOCH FROM lastlogin) AS lastlogin,
                        EXTRACT(EPOCH FROM firstlogin) AS firstlogin,
                        enabled
                        FROM users

The PHP I'm using to format the result is currently:

strftime("%d %b %Y / %H:%M %Z", $val["lastlogin"]);

Before I was doing the same thing with date() and was getting the exact 
same result.

Thanks!

-Jesse