I'm in Florida (Eastern Time Zone / New York), using Ubuntu 10.04LTS /
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'
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