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

Hello Jonathan,

the problem might be solved after the upcoming weekend.

Because on the upcoming weekend most other countries of the world
switch time.

Some years ago USA was conform here - but then USA
changed the time switching date for USA (or just for some states).
Maybe that is the reason. Your system not got the update that USA is
switching on another date then almost the rest of the world.

Susanne

On 21.03.2011 14:24, Jonathan Brinkman wrote:
> To make this even weirder, this effect only seems to happen to the
> 'postgres' user. When I use the 'bucardo' user, the time zone is correct!
>
> postgres(at)Cloud-DB1:~$ psql beta_cms_main -c "select now();"
> now
> -------------------------------
> 2011-03-21 08:22:37.521213-05
> (1 row)
>
> postgres(at)Cloud-DB1:~$ psql beta_cms_main -c "SHOW TIME ZONE;"
> TimeZone
> ----------
> EST
> (1 row)
>
> postgres(at)Cloud-DB1:~$ su - bucardo
> Password:
> bucardo(at)Cloud-DB1:~$ psql beta_cms_main -c "SHOW TIME ZONE;"
> TimeZone
> ------------------
> America/New_York
> (1 row)
>
> bucardo(at)Cloud-DB1:~$ psql beta_cms_main -c "select now();"
> now
> -------------------------------
> 2011-03-21 09:23:03.079692-04
> (1 row)
>
> bucardo(at)Cloud-DB1:~$ logout
> postgres(at)Cloud-DB1:~$ psql beta_cms_main -c "SHOW TIME ZONE;"
> TimeZone
> ----------
> EST
> (1 row)
>
> -----Original Message-----
> From: Jonathan Brinkman [mailto:JB(at)BlackSkyTech(dot)com]
> Sent: Monday, March 21, 2011 9:14 AM
> To: 'Tom Lane'; 'Kevin Grittner'
> Cc: 'pgsql-bugs(at)postgresql(dot)org'
> Subject: RE: [BUGS] TO_CHAR(timestamptz,datetimeformat) wrong after DST
> change
>
> I understand now that I must use America/New_York for DST to function. I
> see in select * from pg_timezone_names ; that 'EDT' is a shortcut. I tried
> to SET TIME ZONE 'EDT'; but PG doesn't seem to like that.
>
> My problem is that the corrected time zone (America/New_York) doesn't seem
> to stick after updating. I update it in psql (cmd line) and within psql it
> returns correctly. But when I then view now() from command line the DST
> change is not there and time zone is again 'EST'. So:
>
> postgres(at)Cloud-DB1:~$ psql beta_cms_main -c "SHOW TIME ZONE;"
> TimeZone
> ----------
> EST
> (1 row)
>
> postgres(at)Cloud-DB1:~$ psql beta_cms_main
> psql (8.4.7)
> Type "help" for help.
>
> beta_cms_main=# show time zone;
> TimeZone
> ----------
> EST
> (1 row)
>
> beta_cms_main=# set time zone 'America/New_York';
> SET
> beta_cms_main=# show time zone;
> TimeZone
> ------------------
> America/New_York
> (1 row)
>
> beta_cms_main=# \q
>
> postgres(at)Cloud-DB1:~$ psql beta_cms_main -c "SHOW TIME ZONE;"
> TimeZone
> ----------
> EST
> (1 row)
>
> postgres(at)Cloud-DB1:~$ psql beta_cms_main -c "select now();"
> now
> -------------------------------
> 2011-03-21 08:09:07.029884-05
> (1 row)
>
> [INCORRECT, SHOULD BE -04 and it is now 9:09AM, not 8:09AM]
>
> I enabled America/New_York in postgresql.conf and restarted PG but no
> change.
> I re-ran tzdata in Ubuntu but no change.
> I rebooted the server no change.
>
>
>
>
>
>
>
> -----Original Message-----
> From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
> Sent: Friday, March 18, 2011 12:47 PM
> To: Kevin Grittner
> Cc: JB(at)BlackSkyTech(dot)com; pgsql-bugs(at)postgresql(dot)org
> Subject: Re: [BUGS] TO_CHAR(timestamptz,datetimeformat) wrong after DST
> change
>
> "Kevin Grittner"<Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
>> "Jonathan Brinkman"<JB(at)BlackSkyTech(dot)com> wrote:
>>> I guess EST is not DST-friendly?
>
>> EST stands for Eastern *Standard* Time, which is explicitly *not*
>> under Daylight Saving Time.
> Right. SET TIMEZONE 'EST' gets you GMT-5 all year round.
> For background see this bit in src/timezone/data/northamerica:
>
> # From Arthur David Olson, 2005-12-19
> # We generate the files specified below to guard against old files with
> # obsolete information being left in the time zone binary directory.
> # We limit the list to names that have appeared in previous versions of
> # this time zone package.
> # We do these as separate Zones rather than as Links to avoid problems if
> # a particular place changes whether it observes DST.
> # We put these specifications here in the northamerica file both to
> # increase the chances that they'll actually get compiled and to
> # avoid the need to duplicate the US rules in another file.
>
> # Zone NAME GMTOFF RULES FORMAT [UNTIL]
> Zone EST -5:00 - EST
> Zone MST -7:00 - MST
> Zone HST -10:00 - HST
> Zone EST5EDT -5:00 US E%sT
> Zone CST6CDT -6:00 US C%sT
> Zone MST7MDT -7:00 US M%sT
> Zone PST8PDT -8:00 US P%sT
>
> (Note: the lack of a RULES entry means no DST rule.)
>
> regards, tom lane
>
>

--
Susanne Ebrecht - 2ndQuadrant
PostgreSQL Development, 24x7 Support, Training and Services
www.2ndQuadrant.com