When in a command prompt I log into psql with merely "psql" and get postgres=#, and run SELECT now(); I get the correct time. When I log into my application's database with psql beta_cms_gate and get beta_cms_gate=# prompt, and run SELECT now(); I get incorrect time (still -05 timezone, and 1 hour too early).
When I use psql and show time zone; I get "America/New_York". When I use psql beta_cms_gate and show time zone; I get "EST". I guess EST is not DST-friendly? My postgresql.conf is set to "America/New York". Within my DB I ran set time zone 'America/New_York'; and retried the select now(), and now it is correct. -----Original Message----- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Friday, March 18, 2011 11:42 AM To: j...@blackskytech.com Cc: pgsql-bugs@postgresql.org Subject: Re: [BUGS] TO_CHAR(timestamptz,datetimeformat) wrong after DST change "Jonathan Brinkman" <j...@blackskytech.com> writes: > My TO_CHAR function is now an hour off thanks to Daylight Savings Time. > The dates are correct (I'm in EST: TZ=-04) but my function now returns TIME > an hour early. > (prior to DST we were TZ=-05). > TIMESTAMPTZ data (v_dt): 2011-03-17 18:21:50-04 > FUNCTION SNIPPET: to_char(v_dt, 'mm/dd/yyyy hh:mi AM') > FUNCTION RETURNS: 03/18/2011 09:21 AM > FUNCTION SHOULD RETURN: 03/18/2011 10:21 AM > postgres=# show time zone; > TimeZone > ------------------ > America/New_York > (1 row) Works for me: regression=# set timezone = 'America/New_York'; SET regression=# select now(); now ------------------------------- 2011-03-18 11:39:45.124162-04 (1 row) regression=# select to_char(now(), 'mm/dd/yyyy hh:mi AM'); to_char --------------------- 03/18/2011 11:39 AM (1 row) Are you sure your application is running with the timezone setting you think it is? regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
