Hi!

Seeing no comments on the same issue I raised in=20
pgsql-general list, I am posting it here.
The documentation (Appendix B.2. Date/Time Key Words) says=20
that the following SQL's are legal, but actually they are=20
not:

SET TIME ZONE TO '<any time zone abbreviation>'
(examples:
SET TIMEZONE TO 'NZDT';
SET TIMEZONE TO 'EST';
)

However, the following SQL's are accepted by postgres:

SET TIME ZONE TO 0
SET TIME ZONE TO 9
SET TIME ZONE TO -4

Regards,
CN

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

"cnliou" <cnliou (AT) so-net (DOT) net.tw> writes:
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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Thank you! Tom,


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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

"cnliou" <cnliou (AT) so-net (DOT) net.tw> writes:
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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

¡° Include¡m"Tom Lane" <tgl (AT) sss (DOT) pgh.pa.us>¡nwrote:
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

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

"cnliou" <cnliou (AT) so-net (DOT) net.tw> writes:
"SET TIME ZONE 8.5" works, as does "SET TIME ZONE INTERVAL '08:30';"

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)