From: Jesse Scott <scotje@wwc.edu>
To: Postgresql SQL Mailing List <pgsql-sql@postgresql.org>
Subject: Timezone troubles
Date: Wed, 13 Aug 2003 20:20:59 -0700
Message-ID: <3F3B001B.8060506@wwc.edu> (raw)
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
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: <3F3B001B.8060506@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