Re: Timestamp to time_t

On Sep 15, 2009, at 8:50 AM, Tom Lane wrote:

> See TIMEZONE_HOUR, TIMEZONE_MINUTE field specifications, in particular
>
>            b) Otherwise, let TZ be the interval value of the implicit
>              or explicit time zone associated with the <datetime value
>              expression>. If <extract field> is TIMEZONE_HOUR, then  
> the
>              result is calculated as
>
>                 EXTRACT (HOUR FROM TZ)
>
>              Otherwise, the result is calculated as
>
>                 EXTRACT (MINUTE FROM TZ)
>
> I haven't tracked down whether the phrase "implicit or explicit time
> zone" is hiding any interesting weasel words, but it sure *looks* like
> you are supposed to be able to pull out the TZ offset

try=# select extract(timezone_hour from '2001-02-16 20:38:40 America/ 
Los_Angeles'::timestamptz); date_part
-----------         0
(1 row)

That doesn't look right. AFAICT, timestamptz converts the TZ to the  
default TZ and discards the specified TZ. Same with offsets:

try=# select extract(timezone_hour from '2001-02-16 20:38:40  
-08:00'::timestamptz);    date_part
-----------         0
(1 row)

Best,

David