Re: time zone problem

On Monday, January 16, 2012 5:50:42 am Cefull Lo wrote:
> Hi,
> I figure it out.
> If only the offset from UTC is given, you may try
>
> select current_timestamp at time zone (select name from
> pg_catalog.pg_timezone_names where utc_offset= interval '+8 hours' limit 1)
>
> Would give the exact timestamp at those time zone.

This assumes the current_timestamp is being run against a server that has
timezone with no DST component. If not the answer will be different when DST is
in effect versus when it is not. Remember a timezone has an offset , but an offset
is not any given timezone.

To illustrate using your example above without the limit:

test(5432)postgres=#select name from pg_catalog.pg_timezone_names where
utc_offset= interval '+8 hours';
        name
--------------------
 Antarctica/Casey
 Asia/Brunei
 Asia/Harbin
 Asia/Shanghai
 Asia/Chongqing
 Asia/Urumqi
 Asia/Kashgar
 Asia/Hong_Kong
 Asia/Taipei
 Asia/Macau
 Asia/Makassar
 Asia/Kuala_Lumpur
 Asia/Kuching
 Asia/Ulaanbaatar
 Asia/Choibalsan
 Asia/Manila
 Asia/Singapore
 Asia/Krasnoyarsk
 Asia/Chungking
 Asia/Macao
 Asia/Ujung_Pandang
 Asia/Ulan_Bator
 Australia/Perth
 Australia/West
 Etc/GMT-8
 Hongkong
 PRC
 ROC
 Singapore


>


--
Adrian Klaver
adrian.klaver@gmail.com