By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,179 Members | 970 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,179 IT Pros & Developers. It's quick & easy.

TIMEZONE not working?

P: n/a
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)

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
"cnliou" <cn****@so-net.net.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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.