time zone problem

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

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.

On Fri, Jan 13, 2012 at 11:59 PM, Cefull Lo <cefull@gmail.com> wrote:

Hi everybody,
I'm located in Hong Kong, UTC+8 time zone. When I

select current_timestamp;

gives

now
-------------------------------
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. But the result is

timezone
----------------------------
2012-01-13 07:57:24.407825

How to get the correct timestamp at the time zone I gave?

Thanks in advance

--
-----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