TIMEZONE not working?

From: "cnliou" <cnliou(at)so-net(dot)net(dot)tw>
To: "" <pgsql-general(at)postgresql(dot)org>
Subject: TIMEZONE not working?
Date: 2003-11-26 06:14:05
Message-ID: 1069827245.31512.cnliou@so-net.net.tw
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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)

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Eamon Reyn 2003-11-26 06:41:37 Java Strings and quote marks
Previous Message Shridhar Daithankar 2003-11-26 06:13:41 Re: PostgreSQL is much faster than MySQL, only when...