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.