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.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general