Re: Messed up time zones

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