Thread: Setting time zone commands

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

"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