From: Laszlo Nagy <gandalf@shopzeus.com>
To: pgsql-admin@postgresql.org
Subject: Re: Messed up time zones
Date: Fri, 03 Aug 2012 12:40:04 +0200
Message-ID: <501BAA84.9010306@shopzeus.com> (raw)
In-Reply-To: <501B8C6F.6040100@shopzeus.com>
References: <501B8C6F.6040100@shopzeus.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: gandalf@shopzeus.com, pgsql-admin@postgresql.org
Subject: Re: Messed up time zones
In-Reply-To: <501BAA84.9010306@shopzeus.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox