Content-Type: text/plain; charset=us-ascii
Mime-Version: 1.0 (Mac OS X Mail 7.0 \(1816\))
Subject: Re: displaying UTC time in local time
From: Scott Ribe <scott_ribe@elevated-dev.com>
In-Reply-To: <1383055558685-5776257.post@n5.nabble.com>
Date: Tue, 29 Oct 2013 08:21:02 -0600
Cc: pgsql-admin@postgresql.org
Content-Transfer-Encoding: quoted-printable
Message-Id: <4A6122E3-1BA1-4948-A3A2-D7358CD41E1C@elevated-dev.com>
References: <1382997116262-5776169.post@n5.nabble.com>
 <13452.1383001151@sss.pgh.pa.us> <1383055558685-5776257.post@n5.nabble.com>
To: mark_postgres_user <mark.ikemoto@fireeye.com>
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

On Oct 29, 2013, at 8:05 AM, mark_postgres_user <mark.ikemoto@fireeye.com> =
wrote:

> So, because my timestamps are defined as timestamp without
> timezone data type and are stored in UTC...

No, they're not stored as *any* time zone at all, not even UTC. They are ti=
mestamp *without* time zone.

> I can use AT TIME ZONE to convert
> the displayed time to any timezone including my local timezone.

No, you can use AT TIME ZONE to convert them to a timestamp with time zone =
and simultaneously specify the time zone they are relative to, in other wor=
ds the time zone to which they were adjusted before being stored.

> P.S. Someone else owns the database I'm using so I can't change the
> timestamps to be timestamp with timezone.

OK, you're stuck with an incorrect data type. That's why it's not intuitive=
 to work with. Because it's timestamp without time zone, you have to do the=
 work that PG would do for you if the correct type were used--you have to m=
ake sure the timestamps are all adjusted to the same time zone before being=
 stored, and you have to convert them to local time after retrieval.

--=20
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice


--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin