Date: Mon, 28 Oct 2013 14:51:56 -0700 (PDT)
From: mark_postgres_user <mark.ikemoto@fireeye.com>
To: pgsql-admin@postgresql.org
Message-ID: <1382997116262-5776169.post@n5.nabble.com>
Subject: displaying UTC time in local time
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

In my Postgres 9.1.9 database, I'd like to display a field I created,
=E2=80=9Cstart_time=E2=80=9D, in my =E2=80=9Cmisc=E2=80=9D database table. =
 I=E2=80=99ve defined this field as data
type =E2=80=9Ctimestamp without time zone=E2=80=9D.  Everything that I've r=
ead about
Postgres says that that timestamp fields are stored in UTC format.  So when
these fields are displayed they=E2=80=99ll be converted at that point into =
local and
show up in local timezone time based on what my server is configured for
(currently PDT).

But when I run:

select start_time from misc where id =3D 29944

          start_time
----------------------------
 2013-10-18 06:42:03.974849
(1 row)

I don't get the time displayed in local timezone time =3D Pacific/California
time:
2013-10-17 23:42:03.974849-07

I've tried all sorts of tinkering to get this timestamp to show up in my
local timezone time.  Only this seems to work:

select start_time at time zone 'UTC' from misc where id =3D 29944
           timezone
-------------------------------
 2013-10-17 23:42:03.974849-07
(1 row)

But this doesn't make sense to me.  The timestamp is stored in UTC already.=
=20
So asking it to be coerced into UTC timezone time before being displayed
seems counter-intuitive.

Any clarification is appreciated.  Thanks.


--
View this message in context: http://postgresql.1045698.n5.nabble.com/displ=
aying-UTC-time-in-local-time-tp5776169.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.


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