Message-ID: <501BAA84.9010306@shopzeus.com>
Date: Fri, 03 Aug 2012 12:40:04 +0200
From: Laszlo Nagy <gandalf@shopzeus.com>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:14.0) Gecko/20120714 Thunderbird/14.0
MIME-Version: 1.0
To: pgsql-admin@postgresql.org
Subject: Re: Messed up time zones
References: <501B8C6F.6040100@shopzeus.com>
In-Reply-To: <501B8C6F.6040100@shopzeus.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 2012-08-03 10:31, Laszlo Nagy wrote:
> select abbrev,utc_offset,count(*) from pg_timezone_names
> where abbrev='EST'
> group by abbrev,utc_offset
>
> There are 12 times zones with 'EST' code, offset = GMT+10. And there 
> are 8 time zones with 'EST' code, offset= GMT+5 at the same time!
Sorry, I still have some questions.

template1=> set time zone 'UTC';
template1=> select to_char(('2011-10-30 00:00:00'::timestamp at time 
zone 'UTC') at time zone 'Europe/Budapest', 'YYYY-MM-DD HH24:MI:SS TZ');
        to_char
----------------------
  2011-10-30 02:00:00
(1 row)

template1=> select to_char(('2011-10-30 01:00:00'::timestamp at time 
zone 'UTC') at time zone 'Europe/Budapest', 'YYYY-MM-DD HH24:MI:SS TZ');
        to_char
----------------------
  2011-10-30 02:00:00
(1 row)


The time zone was not included in the output. I guess it is because the 
last "at time zone" part converted the timestamptz into a timestamp. 
Right now, these results don't just look the same. They are actually the 
same values, which is obviously not what I want. They have been 
converted from different UTC values, so they should be different. I 
would like to see "2011-10-30 02:00:00+0600" and "2011-10-30 
02:00:00+0500", or something similar.

So the question is: how do I convert a timestamptz value into a 
different time zone, without changing its type? E.g. it should remain a 
timestamptz, but have a (possibly) different value and a different time 
zone assigned.

Thanks,

    Laszlo