Setting time zone commands

"cnliou" <cnliou@so-net.net.tw> writes:

The documentation (Appendix B.2. Date/Time Key Words) says
that the following SQL's are legal, but actually they are
not:
SET TIME ZONE TO '<any time zone abbreviation>'

The documentation does not actually say any such thing, although its
failure to clarify what it *is* saying isn't great. I have reworded it
as follows in CVS tip:

: Table B-4 shows the time zone abbreviations recognized by PostgreSQL in
: date/time input values. PostgreSQL uses internal tables for time zone
: input decoding, since there is no standard operating system interface to
: provide access to general, cross-time zone information. The underlying
: operating system is used to provide time zone information for output,
: however.
:
: Keep in mind also that the time zone names recognized by SET TIMEZONE
: are operating-system dependent and may have little to do with Table
: B-4. For example, some systems recognize values like 'Europe/Rome' in
: SET TIMEZONE.

We do not make any attempt to document what timezone names are accepted
by SET TIMEZONE, because there is in general no way to find out :-(

regards, tom lane

Thank you! Tom,

The documentation does not actually say any such thing,

although its

failure to clarify what it *is* saying isn't great. I have

reworded it

as follows in CVS tip:

: Table B-4 shows the time zone abbreviations recognized by

PostgreSQL in

: date/time input values. PostgreSQL uses internal tables

for time zone

: input decoding, since there is no standard operating

system interface to

: provide access to general, cross-time zone information.

The underlying

: operating system is used to provide time zone information

for output,

: however.
:
: Keep in mind also that the time zone names recognized by

SET TIMEZONE

: are operating-system dependent and may have little to do

with Table

: B-4. For example, some systems recognize values like

'Europe/Rome' in

: SET TIMEZONE.

We do not make any attempt to document what timezone names

are accepted

by SET TIMEZONE, because there is in general no way to find

out :-(

So, what is the official syntax? Is it like the following?

SET TIME ZONE TO 8:30
SET TIMEZONE TO 8:30
SET TIME ZONE TO -5
SET TIMEZONE TO -5

Regards,

CN

"cnliou" <cnliou@so-net.net.tw> writes:

So, what is the official syntax?

See the SET command's reference page. I believe you need to quote
anything that doesn't look like an identifier or number.

regards, tom lane

�� Include�m"Tom Lane" <tgl@sss.pgh.pa.us>�nwrote:

So, what is the official syntax?

See the SET command's reference page. I believe you need

to quote

anything that doesn't look like an identifier or number.

Thank you very much! You have clarified all my timestamp and
time zone questions except the last minor two as follows:

(1) The query using UTC offset hours (SET TIME ZONE '<0 - 23
integers>') to set time zone will be the legal SQL command
for quite a while, won't it?

(2) What is the correct syntax to set time zones having 30
minutes offset UTC?

db1=# set time zone '08:30';
ERROR: unrecognized time zone name: "08:30"
db1=# set time zone 08:30;
ERROR: syntax error at or near ":" at character 17
db1=# set time zone '-03:30';
ERROR: unrecognized time zone name: "-03:30"
db1=# set time zone 'NST';
ERROR: unrecognized time zone name: "NST"

Regards,
CN

"cnliou" <cnliou@so-net.net.tw> writes:

(2) What is the correct syntax to set time zones having 30
minutes offset UTC?

"SET TIME ZONE 8.5" works, as does "SET TIME ZONE INTERVAL '08:30';"

regards, tom lane