From: mark_postgres_user <mark.ikemoto@fireeye.com>
To: pgsql-admin@postgresql.org
Subject: displaying UTC time in local time
Date: Mon, 28 Oct 2013 14:51:56 -0700 (PDT)
Message-ID: <1382997116262-5776169.post@n5.nabble.com> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
In my Postgres 9.1.9 database, I'd like to display a field I created,
“start_time”, in my “misc” database table. I’ve defined this field as data
type “timestamp without time zone”. Everything that I've read about
Postgres says that that timestamp fields are stored in UTC format. So when
these fields are displayed they’ll 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 = 29944
start_time
----------------------------
2013-10-18 06:42:03.974849
(1 row)
I don't get the time displayed in local timezone time = 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 = 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.
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/displaying-UTC-time-in-local-time-tp5776169.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
--
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: mark.ikemoto@fireeye.com
Subject: Re: displaying UTC time in local time
In-Reply-To: <1382997116262-5776169.post@n5.nabble.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