Обсуждение: Timezone troubles

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

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

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.
>
>  
>

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
>

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): ChangeEXTRACT(EPOCH FROM timestamp) so that a timestamp without
timezoneis assumed to be in local time, not GMT.  This improves consistencywith other operations, which all assume
localtimezone when itmatters.  Per bug #897.
 
        regards, tom lane

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