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(dot)klaver(at)gmail(dot)com