Skip site navigation (1) Skip section navigation (2)

Re: TO_CHAR(timestamptz,datetimeformat) wrong after DST change

From: "Jonathan Brinkman" <JB(at)BlackSkyTech(dot)com>
To: "'Tom Lane'" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: <pgsql-bugs(at)postgresql(dot)org>
Subject: Re: TO_CHAR(timestamptz,datetimeformat) wrong after DST change
Date: 2011-03-18 16:01:25
Message-ID: 005601cbe585$bc7d4b10$3577e130$@com (view raw or flat)
Thread:
Lists: pgsql-bugs
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:tgl(at)sss(dot)pgh(dot)pa(dot)us] 
Sent: Friday, March 18, 2011 11:42 AM
To: JB(at)BlackSkyTech(dot)com
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: [BUGS] TO_CHAR(timestamptz,datetimeformat) wrong after DST
change

"Jonathan Brinkman" <JB(at)BlackSkyTech(dot)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


In response to

Responses

pgsql-bugs by date

Next:From: Kevin GrittnerDate: 2011-03-18 16:33:54
Subject: Re: TO_CHAR(timestamptz,datetimeformat) wrong after DST change
Previous:From: Tom LaneDate: 2011-03-18 15:41:43
Subject: Re: TO_CHAR(timestamptz,datetimeformat) wrong after DST change

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group