LOCALTIMESTAMP has wrong time zone

From: "Jonathan Brinkman" <JB(at)BlackSkyTech(dot)com>
To: <pgsql-bugs(at)postgresql(dot)org>
Subject: LOCALTIMESTAMP has wrong time zone
Date: 2011-02-24 21:16:10
Message-ID: 000001cbd468$124cc720$36e65560$@com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Greetings
I'm in Florida (Eastern Time Zone / New York), using Ubuntu 10.04LTS /
Postgresql 8.4.7.

My default timestamps in my database all are +5 hours in the future
(probably GMT), unless I code the 'EST' time zone into the field's default
(timezone('EST'::text, now())).If I use now() it is +5 hours, and if I use
LOCALTIMESTAMP my SQL editor (EMS Postgresql Mgr) changes it to:
'now'::text::timestamp without time zone, which is also +5 hours.

Anywhere I use LOCALTIMESTAMP in my functions, it is shifted +5 hours in the
future. I must use timezone('EST'::text, now()) everywhere, rather than
CURRENT_TIMESTAMP or LOCALTIMESTAMP. Major pain!!

When I type date in Ubuntu Command line, I see correctly: Thu Feb 24
16:13:13 EST 2011
When I run dpkg-reconfigure tzdata, the linux time zone is correct: America
/ New York.

I've set postgresql.conf --> time zone = 'America/New York'

I've used:
psql beta_main -c "SET TIME ZONE EST;"
psql beta_main -c "SHOW TIME ZONE;"

I've even reinstalled the Database from another server (where this is NOT
happening!) after setting above stuff, and restarted postgresql, same
results.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Kevin Grittner 2011-02-24 22:06:09 Re: LOCALTIMESTAMP has wrong time zone
Previous Message Merlin Moncure 2011-02-24 19:31:22 Re: Function trunc() behaves in unexpected manner with different data types