From: | "Jonathan Brinkman" <jonathanbrinkman(at)yahoo(dot)com> |
---|---|
To: | "'Steve Crawford'" <scrawford(at)pinpointresearch(dot)com> |
Cc: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: TO_CHAR(timestamptz,datetimeformat) wrong after DST change |
Date: | 2011-03-18 17:07:59 |
Message-ID: | 006301cbe58f$08bc6700$1a353500$@com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)pinpointresearch(dot)com]
Sent: Friday, March 18, 2011 1:05 PM
To: jonathansfl
Cc: pgsql-general(at)postgresql(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Tomas Vondra | 2011-03-18 17:30:49 | Re: query taking much longer since Postgres 8.4 upgrade |
Previous Message | Steve Crawford | 2011-03-18 17:05:29 | Re: TO_CHAR(timestamptz,datetimeformat) wrong after DST change |