Updating timezone setting

From: Keith Fiske <keith(at)omniti(dot)com>
To: PGSQL Mailing List <pgsql-general(at)postgresql(dot)org>
Subject: Updating timezone setting
Date: 2014-11-10 22:04:35
Message-ID: CAG1_KcAs+_PofD7uSEg5-5oRrCB_ngmxDEyTWtxWfMcmS4pQBg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

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.

Thanks,

--
Keith Fiske
Database Administrator
OmniTI Computer Consulting, Inc.
http://www.keithf4.com

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Vinay Gupta 2014-11-11 00:03:40 Concat bigint Array Values in a Function
Previous Message David G Johnston 2014-11-10 20:12:50 Re: SSL Certificates in Postgres 9.3 and Windows 7