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