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',
firstlogintimestamp, enabled bool DEFAULT false
) WITH OIDS;
Query: SELECT uid, username, pw_hash, email,
theme, EXTRACT(EPOCH FROM lastlogin) AS lastlogin,
EXTRACT(EPOCHFROM 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