pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedTimezone troubles
6+ messages / 3 participants
[nested] [flat]
* Timezone troubles
@ 2003-08-14 03:20 Jesse Scott <scotje@wwc.edu>
2003-08-14 04:12 ` Re: Timezone troubles Josh Berkus <josh@agliodbs.com>
2003-08-14 11:26 ` Re: Timezone troubles Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 6+ messages in thread
From: Jesse Scott @ 2003-08-14 03:20 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Timezone troubles
2003-08-14 03:20 Timezone troubles Jesse Scott <scotje@wwc.edu>
@ 2003-08-14 04:12 ` Josh Berkus <josh@agliodbs.com>
2003-08-14 04:38 ` Re: Timezone troubles Jesse Scott <scotje@wwc.edu>
1 sibling, 1 reply; 6+ messages in thread
From: Josh Berkus @ 2003-08-14 04:12 UTC (permalink / raw)
To: Jesse Scott <scotje@wwc.edu>; +Cc: pgsql-sql
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.
--
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Timezone troubles
2003-08-14 03:20 Timezone troubles Jesse Scott <scotje@wwc.edu>
2003-08-14 04:12 ` Re: Timezone troubles Josh Berkus <josh@agliodbs.com>
@ 2003-08-14 04:38 ` Jesse Scott <scotje@wwc.edu>
2003-08-14 04:59 ` Re: Timezone troubles Jesse Scott <scotje@wwc.edu>
0 siblings, 1 reply; 6+ messages in thread
From: Jesse Scott @ 2003-08-14 04:38 UTC (permalink / raw)
To: pgsql-sql
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.
>
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Timezone troubles
2003-08-14 03:20 Timezone troubles Jesse Scott <scotje@wwc.edu>
2003-08-14 04:12 ` Re: Timezone troubles Josh Berkus <josh@agliodbs.com>
2003-08-14 04:38 ` Re: Timezone troubles Jesse Scott <scotje@wwc.edu>
@ 2003-08-14 04:59 ` Jesse Scott <scotje@wwc.edu>
2003-08-14 11:20 ` Re: Timezone troubles Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Jesse Scott @ 2003-08-14 04:59 UTC (permalink / raw)
To: pgsql-sql
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
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Timezone troubles
2003-08-14 03:20 Timezone troubles Jesse Scott <scotje@wwc.edu>
2003-08-14 04:12 ` Re: Timezone troubles Josh Berkus <josh@agliodbs.com>
2003-08-14 04:38 ` Re: Timezone troubles Jesse Scott <scotje@wwc.edu>
2003-08-14 04:59 ` Re: Timezone troubles Jesse Scott <scotje@wwc.edu>
@ 2003-08-14 11:20 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2003-08-14 11:20 UTC (permalink / raw)
To: Jesse Scott <scotje@wwc.edu>; +Cc: pgsql-sql
Jesse Scott <scotje@wwc.edu> writes:
>> 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.
In that case I think you are looking for this 7.3.3 fix:
2003-02-27 16:37 tgl
* src/backend/utils/adt/timestamp.c (REL7_3_STABLE): Change
EXTRACT(EPOCH FROM timestamp) so that a timestamp without time zone
is assumed to be in local time, not GMT. This improves consistency
with other operations, which all assume local timezone when it
matters. Per bug #897.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Timezone troubles
2003-08-14 03:20 Timezone troubles Jesse Scott <scotje@wwc.edu>
@ 2003-08-14 11:26 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 6+ messages in thread
From: Tom Lane @ 2003-08-14 11:26 UTC (permalink / raw)
To: Jesse Scott <scotje@wwc.edu>; +Cc: pgsql-sql
Jesse Scott <scotje@wwc.edu> writes:
> lastlogin timestamp DEFAULT 'now',
> firstlogin timestamp,
> EXTRACT(EPOCH FROM lastlogin) AS lastlogin,
> EXTRACT(EPOCH FROM firstlogin) AS firstlogin,
Btw ... although you are hitting a bug in EXTRACT(), I think the above
is pretty foolish. Timestamp without time zone is inherently not a well
defined point in time --- its meaning depends on the eye of the
beholder, or at least the timezone the beholder lives in. Timestamp
with time zone is what you want for recording the true time of any
well-defined event. (Defaulting "timestamp" to mean "without time zone"
is one of the many bad decisions that have been forced on us by SQL spec
compliance.)
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2003-08-14 11:26 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-08-14 03:20 Timezone troubles Jesse Scott <scotje@wwc.edu>
2003-08-14 04:12 ` Josh Berkus <josh@agliodbs.com>
2003-08-14 04:38 ` Jesse Scott <scotje@wwc.edu>
2003-08-14 04:59 ` Jesse Scott <scotje@wwc.edu>
2003-08-14 11:20 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-08-14 11:26 ` Tom Lane <tgl@sss.pgh.pa.us>
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