Re: Updating timezone setting - Mailing list pgsql-general

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@aklaver.com