Re: TIMEZONE not working?

Hi!

If I correctly understand v7.4 manual, value, say,
'2003-11-26 12:00' in TIMESTAMP WITHOUT TIMEZONE column
should output '2003-11-26 19:00' for "+08:00" timezone.

The following test results seem to be somewhat unexpected.
Restting OS timezone (/etc/timezone and /etc/localtime in
Linux) does not make the results more comfortable.

The display in Windowz for the result set SELECTed from
PostgreSQL running on Linux is the same as that in psql
prompt - being always UTC value. Query 'SET SESSION TIMEZONE
TO 8' is sent to the backend after the connection with the
backend is established and before query 'SELECT * FROM ..'
is sent to the backend, of course.

Any idea would be much appreciated!

Regards,

CN
=============
db1=# \d test
Table "public.test"
Column | Type | Modifiers
--------+-----------------------------+-----------
f1 | timestamp without time zone |

db1=# SHOW TimeZone;
TimeZone
----------
unknown
(1 row)

db1=# SET TIME ZONE CCT;
ERROR: unrecognized time zone name: "cct"
db1=# SET TIME ZONE 'CCT';
ERROR: unrecognized time zone name: "CCT"
db1=# SET TIME ZONE TO 'JST';
ERROR: syntax error at or near "TO" at character 15
db1=# SET TIME ZONE 8;
SET
db1=# SHOW TimeZone;
TimeZone
----------
08:00:00
(1 row)

db1=# INSERT INTO test VALUES ('2003-11-26 13:00');
INSERT 40397 1
db1=# SHOW DateStyle;
DateStyle
-----------
ISO, MDY
(1 row)

db1=# SET DateStyle 'ISO, YMD';
ERROR: syntax error at or near "'ISO, YMD'" at character 15
db1=# SET DateStyle TO 'ISO, YMD';
SET
db1=# SELECT * FROM test;
f1
---------------------
2003-11-26 13:00:00
(1 row)

db1=# SET DateStyle TO 'ISO, DMY';
SET
db1=# SHOW DateStyle;
DateStyle
-----------
ISO, DMY
(1 row)

db1=# SELECT * FROM test;
f1
---------------------
2003-11-26 13:00:00
(1 row)

db1=# SET TimeZone TO -1;
SET
db1=# SHOW Timezone;
TimeZone
-----------
-01:00:00
(1 row)

db1=# SELECT * FROM test;
f1
---------------------
2003-11-26 13:00:00
(1 row)

"cnliou" <cnliou(at)so-net(dot)net(dot)tw> writes:
> If I correctly understand v7.4 manual, value, say,
> '2003-11-26 12:00' in TIMESTAMP WITHOUT TIMEZONE column
> should output '2003-11-26 19:00' for "+08:00" timezone.

Not at all. TIMESTAMP WITHOUT TIMEZONE will not react to timezone
environment at all. What part of the docs gave you another impression?

regards, tom lane