Thread: time zone problem

Hi everybody,

Cefull Lo <cefull@gmail.com> writes:
> I'm located in Hong Kong, UTC+8 time zone. When I
> select current_timestamp;
>  2012-01-13 23:56:16.825558+08

> However, when I
> select current_timestamp at time zone 'UTC+8';
> I expect the result is the same as the above one.

Sorry, but it isn't.  A time zone name spelled that way is a POSIX time
zone specification, and in POSIX positive offsets are west of Greenwich,
not east.  See
http://www.postgresql.org/docs/9.1/static/datatype-datetime.html#DATATYPE-TIMEZONES

            regards, tom lane

On 01/13/2012 08:18 AM, Tom Lane wrote:
> Cefull Lo<cefull@gmail.com>  writes:
>> I'm located in Hong Kong, UTC+8 time zone. When I
>> select current_timestamp;
>>   2012-01-13 23:56:16.825558+08
>> However, when I
>> select current_timestamp at time zone 'UTC+8';
>> I expect the result is the same as the above one.
> Sorry, but it isn't.  A time zone name spelled that way is a POSIX time
> zone specification, and in POSIX positive offsets are west of Greenwich,
> not east.  See
> http://www.postgresql.org/docs/9.1/static/datatype-datetime.html#DATATYPE-TIMEZONES
>
>             regards, tom lane
>

However "at time zone 'Hongkong'" might give you what you want:

select current_timestamp, current_timestamp at time zone 'Hongkong';

               now              |          timezone
-------------------------------+----------------------------
  2012-01-14 00:32:46.217178+08 | 2012-01-14 00:32:46.217178

Cheers,
Steve

--
-----BEGIN GEEK CODE BLOCK-----
Version: 3.1
GSC d- s:>++ a- C++ UL/B+++$ !P L++>+++++
E--- W+ N o-- K? w++ O? M- V- PS PE++(-) Y+
PGP++@ t 5 X R+>+++$ tv- b+++ DI++ D++
G++@ e+++>+++++ h*>--- r-- z?
-----END GEEK CODE BLOCK------

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

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.

Meant to add to my previous post.
If you want to use a fixed interval then the above can simplified to:

select current_timestamp at time zone  interval '+8 hours'


--
Adrian Klaver
adrian.klaver@gmail.com