From: Tom Lane <tgl@sss.pgh.pa.us>
To: mark_postgres_user <mark.ikemoto@fireeye.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: displaying UTC time in local time
Date: Mon, 28 Oct 2013 18:59:11 -0400
Message-ID: <13452.1383001151@sss.pgh.pa.us> (raw)
In-Reply-To: <1382997116262-5776169.post@n5.nabble.com>
References: <1382997116262-5776169.post@n5.nabble.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
mark_postgres_user <mark.ikemoto@fireeye.com> writes:
> 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).
Those statements apply to values of type timestamp WITH timezone.
A timestamp WITHOUT timezone is just what-you-see-is-what-you-get.
You can use AT TIME ZONE to rotate the value to another zone anyway,
but it's a bit difficult to wrap your head around what will happen.
IIRC, that operator will assume that the without-time-zone value is
expressed in the time zone you name as the other operand, and rotate it
from there to UTC, producing a value of timestamp WITH time zone. That's
okay so far, but remember that when you display the result, it'll get
rotated again (to whatever zone you have set in the "timezone" parameter).
Generally speaking, if you're storing values that represent well-defined
instants in time, you're best off using timestamp with timezone.
Timestamp without timezone has some use to represent concepts like
"the meeting is at 4pm local time Tuesday", where you're aware that
this isn't actually a well-specified instant without some context;
or if you really don't want the system doing *anything* for you time
zone wise.
regards, tom lane
--
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: tgl@sss.pgh.pa.us, mark.ikemoto@fireeye.com
Subject: Re: displaying UTC time in local time
In-Reply-To: <13452.1383001151@sss.pgh.pa.us>
* 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