Re: time zone problem

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