On 11/10/2014 02:04 PM, Keith Fiske wrote:
Discovered that a client of ours had assumed their database was running
in UTC, but turned out to be running in 'US/Eastern'. They had changed
all their systems a while ago to run in UTC but didn't realize the
database was independent of that. The postgresql.conf has
timezone = 'US/Eastern'
set in it. It also looks like they have all their timestamp columns set
to "timestamp without time zone". But their application has been running
in UTC, so the times being inserted are the time in UTC. So when queries
are run locally in pql, they appear to be ahead of now(), which would be
expected.
They'd like to get this fix, at least so that the database is running in
UTC. Is there any issue with just changing the postgresql.conf timezone
to GMT and restarting? All of their applications are inserting time in
UTC anyway, and have been for as long as they can remember.
A quick test:
psql (9.3.5)
Type "help" for help.
test=# \d timestamp_test
Table "public.timestamp_test"
Column | Type | Modifiers
--------+-----------------------------+-----------
id | integer |
ts | timestamp without time zone |
ts_z | timestamp with time zone |
test=# show timezone;
TimeZone
------------
US/Pacific
(1 row)
test=# insert into timestamp_test values (1, now(), now());
INSERT 0 1
test=# insert into timestamp_test values (2, now() at time zone 'utc',
now());
INSERT 0 1
test=# set timezone='utc';
SET
test=# show timezone;
TimeZone
----------
UTC
(1 row)
test=# insert into timestamp_test values (3, now() at time zone 'utc',
now());
INSERT 0 1
test=# select * from timestamp_test ;
id | ts | ts_z
----+----------------------------+-------------------------------
1 | 2014-11-10 17:01:20.675443 | 2014-11-11 01:01:20.675443+00
2 | 2014-11-11 01:01:33.484436 | 2014-11-11 01:01:33.484436+00
3 | 2014-11-11 01:01:48.074499 | 2014-11-11 01:01:48.074499+00
(3 rows)
test=# set timezone='US/Pacific';
SET
test=# show timezone;
TimeZone
------------
US/Pacific
(1 row)
test=# select * from timestamp_test ;
id | ts | ts_z
----+----------------------------+-------------------------------
1 | 2014-11-10 17:01:20.675443 | 2014-11-10 17:01:20.675443-08
2 | 2014-11-11 01:01:33.484436 | 2014-11-10 17:01:33.484436-08
3 | 2014-11-11 01:01:48.074499 | 2014-11-10 17:01:48.074499-08
(3 rows)
Would there be any issues then just leaving the columns as "timestamp
without time zone"? I know that's not ideal, but that would be a big
project to try and convert every single one of those columns.
The plus for converting is that timestamp with timezone anchors the time
and you do not have to worry as much, about what the application is
doing. That being said, I could see rolling that out over time:)
Thanks,
--
Keith Fiske
Database Administrator
OmniTI Computer Consulting, Inc.
http://www.keithf4.com
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
