Thread: extract(timezone_hour) funny business

peter=# select current_timestamp;         timestamptz
-------------------------------2001-10-10 01:04:54.965162+02
(1 row)

peter=# select extract(timezone_hour from current_timestamp);date_part
-----------       -2
(1 row)

Plus or minus?

peter=# select extract(timezone_hour from timestamp '2001-10-10 01:04:54.965162+02');date_part
-----------       -2
(1 row)

(Same problem)

peter=# select extract(timezone_hour from timestamp '2001-10-10 01:04:54.965162+03');
                                           ^^date_part
 
-----------       -2
(1 row)

Big problem.

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter

> Plus or minus?

Is there a standard for this? We are printing date/time using Posix
conventions, which are opposite from the SQL conventions for setting
time zone (which we don't yet support, since it is fundamentally useless
;) I apparently implemented one, and you expect the other.

> peter=# select extract(timezone_hour from timestamp '2001-10-10 01:04:54.965162+03');
> -----------
>         -2
> Big problem.

Not really. The timestamp you have specified is read in and internalized
as a gmt value, then is rewritten using your current time zone settings.
afaict the time zone on an input value should not persist with the value
itself, so the info does not carry far enough forward to be used for an
output routine.

Note that I did not implement "time with time zone" this way, but rather
used a "persistant time zone". I *think* that this should be taken out,
but further discussion is welcome. The reference books are distressingly
unclear or obviously incorrect on this topic, presumably in the
interests of remaining lucid.
                    - Thomas