Thread: TO_CHAR(timestamptz,datetimeformat) wrong after DST change
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)
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/TO-CHAR-timestamptz-datetimeformat-wrong-after-DST-change-tp3966319p3966319.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 03/18/2011 07:59 AM, jonathansfl wrote: > 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) > You are leaving out something critical. Based on what you've shown us, you shouldn't get either answer, you should get "03/17/2011 06:21 PM". select to_char(timestamptz '2011-03-17 18:21:50-04', 'mm/dd/yyyy hh:mi AM'); to_char ----------- 03/17/2011 06:21 PM My money is on an accidental conversion between timestamp and timestamptz in some part of the function you haven't shown us. Cheers, Steve
I was setting TIME ZONE to 'EST' in my formatting function, and I've now learned that EST is NOT the same as 'America/New_York', as EST is not DST-sensitive. I mistyped, the 2011-03-17 18:21:50-04 should have been 2011-03-17 10:21:50-04 Thank you all!! -----Original Message----- From: Steve Crawford [mailto:scrawford@pinpointresearch.com] Sent: Friday, March 18, 2011 1:05 PM To: jonathansfl Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] TO_CHAR(timestamptz,datetimeformat) wrong after DST change On 03/18/2011 07:59 AM, jonathansfl wrote: > 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) > You are leaving out something critical. Based on what you've shown us, you shouldn't get either answer, you should get "03/17/2011 06:21 PM". select to_char(timestamptz '2011-03-17 18:21:50-04', 'mm/dd/yyyy hh:mi AM'); to_char ----------- 03/17/2011 06:21 PM My money is on an accidental conversion between timestamp and timestamptz in some part of the function you haven't shown us. Cheers, Steve