Обсуждение: displaying UTC time in local time

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.

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

> Those statements apply to values of type timestamp WITH timezone.
> A timestamp WITHOUT timezone is just what-you-see-is-what-you-get.

Thanks for this clarification.

To recap:  So, because my timestamps are defined as timestamp without
timezone data type and are stored in UTC, when I display them in their
default form they'll be displayed in UTC.  I can use AT TIME ZONE to convert
the displayed time to any timezone including my local timezone.

So why is it that I can get the displayed time to be local only if I use "AT
TIMEZONE 'UTC'" ?  Anything else will either be ignored or display UTC.

Is there a more intuitive way of getting these timestamps displayed in local
time?

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





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

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
theyare 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
withouttime zone, you have to do the work that PG would do for you if the correct type were used--you have to make sure
thetimestamps are all adjusted to the same time zone before being stored, and you have to convert them to local time
afterretrieval. 

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

mark_postgres_user <mark.ikemoto@fireeye.com> writes:
> To recap:  So, because my timestamps are defined as timestamp without
> timezone data type and are stored in UTC, when I display them in their
> default form they'll be displayed in UTC.  I can use AT TIME ZONE to convert
> the displayed time to any timezone including my local timezone.

> So why is it that I can get the displayed time to be local only if I use "AT
> TIMEZONE 'UTC'" ?  Anything else will either be ignored or display UTC.

When applied to a timestamp-without-timezone, AT TIME ZONE means "convert
argument 1 to timestamp-with-timezone, assuming that it represents local
time in the timezone named by argument 2".  So if your stored values do
in fact represent UTC times, the *only* correct application of AT TIME
ZONE to them is "AT TIME ZONE 'UTC'"; anything else will generate a wrong
ts-with-tz value.

Once you have a correct value, the output function for datatype
timestamp-with-timezone automatically takes care of displaying it in
the zone defined by the timezone parameter (which I assume you've got
set to whatever you think local time is).

If you really want to do all this manually, you could do something like

   SELECT (ts-without-tz-value AT TIME ZONE 'UTC') AT TIME ZONE 'foo'

which will first convert the ts-without-tz value to ts-with-tz under the
assumption that it represents local time in UTC, and then convert the
ts-with-tz value back to ts-without-tz, producing the correct local time
in zone 'foo'.  Since it's now ts-without-tz, it'll be displayed as-is,
without any consultation of the timezone parameter.

But TBH it seems clear to me that you did this wrong.  The way you are
thinking about this column says that you should be storing it as
ts-with-tz.  Then it'll automatically be displayed in your local zone,
and if you want to see it in some other zone, a single application of
AT TIME ZONE will do that for you.

            regards, tom lane