Timezone troubles

From: Jesse Scott <scotje(at)wwc(dot)edu>
To: Postgresql SQL Mailing List <pgsql-sql(at)postgresql(dot)org>
Subject: Timezone troubles
Date: 2003-08-14 03:20:59
Message-ID: 3F3B001B.8060506@wwc.edu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Abdul Wahab Dahalan 2003-08-14 03:22:21 Re: How to speeed up the query performance
Previous Message Stephan Szabo 2003-08-14 03:12:24 Re: How to speeed up the query performance