Thread: 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@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

Hello! Tom,

>Not at all.  TIMESTAMP WITHOUT TIMEZONE will not react to
timezone
>environment at all.

Absolutely right! I seemed to have trouble understanding
lengthy, though good, documentation.

Here are some minor issues I have encountered:

- SQL commands like "SET TIMEZONE TO NZDT" are illegal while
Table B-4 in Appendix B says they are recognized.
- Command "SET TIMEZONE TO +08:30" is also illegal.
- I don't fully understand the statement in section 8.5 of
the documentation:

[QUOTE]
Note: When timestamp values are stored as double precision
floating-point numbers (currently the default), the
effective limit of precision may be less than 6. timestamp
values are stored as seconds since 2000-01-01, and
microsecond precision is achieved for dates within a few
years of 2000-01-01, but the precision degrades for dates
further away.
[/QUOTE]

Does this mean double timestamp, the default storage type,
allows dates starting from 2000-1-1? I just inserted and
selected the value '1999-1-1' without problem.

[QUOTE]
When timestamp values are stored as eight-byte integers (a
compile-time option), microsecond precision is available
over the full range of values. However eight-byte integer
timestamps have a reduced range of dates from 4713 BC up to
294276 AD.
[/QUOTE]

Dos this mean that 8-byte timestamp accepts only up to year
AD 806 (=294276/365)? Table 8-9 looks to me that pgsql
accepts up to AD 5874897 days.

As always, thank you very much for the help!

Best Regards,

CN

On Wednesday 26 November 2003 09:02, cnliou wrote:
> [QUOTE]
> Note: When timestamp values are stored as double precision
> floating-point numbers (currently the default), the
> effective limit of precision may be less than 6. timestamp
> values are stored as seconds since 2000-01-01, and
> microsecond precision is achieved for dates within a few
> years of 2000-01-01, but the precision degrades for dates
> further away.
> [/QUOTE]
>
> Does this mean double timestamp, the default storage type,
> allows dates starting from 2000-1-1? I just inserted and
> selected the value '1999-1-1' without problem.

No, it means that if you have a timestamp such as
  2003-11-26 10:15:27.842204+00
then the .842204 part (microseconds) gets less accurate the further away the
dates get.

--
  Richard Huxton
  Archonet Ltd