From: Scott Ribe <scott_ribe@elevated-dev.com>
To: mark_postgres_user <mark.ikemoto@fireeye.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: displaying UTC time in local time
Date: Tue, 29 Oct 2013 08:21:02 -0600
Message-ID: <4A6122E3-1BA1-4948-A3A2-D7358CD41E1C@elevated-dev.com> (raw)
In-Reply-To: <1383055558685-5776257.post@n5.nabble.com>
References: <1382997116262-5776169.post@n5.nabble.com>
<13452.1383001151@sss.pgh.pa.us>
<1383055558685-5776257.post@n5.nabble.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
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 timestamp *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 words 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 make 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.
--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
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-admin@postgresql.org
Cc: scott_ribe@elevated-dev.com, mark.ikemoto@fireeye.com
Subject: Re: displaying UTC time in local time
In-Reply-To: <4A6122E3-1BA1-4948-A3A2-D7358CD41E1C@elevated-dev.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