Re: TO_CHAR(timestamptz,datetimeformat) wrong after DST change - Mailing list pgsql-bugs
From | Jonathan Brinkman |
---|---|
Subject | Re: TO_CHAR(timestamptz,datetimeformat) wrong after DST change |
Date | |
Msg-id | 000f01cbe7cb$5ca07cb0$15e17610$@com Whole thread Raw |
In response to | TO_CHAR(timestamptz,datetimeformat) wrong after DST change ("Jonathan Brinkman" <JB@BlackSkyTech.com>) |
Responses |
Re: TO_CHAR(timestamptz,datetimeformat) wrong after DST change
|
List | pgsql-bugs |
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@Cloud-DB1:~$ psql beta_cms_main -c "select now();" now ------------------------------- 2011-03-21 08:22:37.521213-05 (1 row) postgres@Cloud-DB1:~$ psql beta_cms_main -c "SHOW TIME ZONE;" TimeZone ---------- EST (1 row) postgres@Cloud-DB1:~$ su - bucardo Password: bucardo@Cloud-DB1:~$ psql beta_cms_main -c "SHOW TIME ZONE;" TimeZone ------------------ America/New_York (1 row) bucardo@Cloud-DB1:~$ psql beta_cms_main -c "select now();" now ------------------------------- 2011-03-21 09:23:03.079692-04 (1 row) bucardo@Cloud-DB1:~$ logout postgres@Cloud-DB1:~$ psql beta_cms_main -c "SHOW TIME ZONE;" TimeZone ---------- EST (1 row) -----Original Message----- From: Jonathan Brinkman [mailto:JB@BlackSkyTech.com] Sent: Monday, March 21, 2011 9:14 AM To: 'Tom Lane'; 'Kevin Grittner' Cc: 'pgsql-bugs@postgresql.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@Cloud-DB1:~$ psql beta_cms_main -c "SHOW TIME ZONE;" TimeZone ---------- EST (1 row) postgres@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@Cloud-DB1:~$ psql beta_cms_main -c "SHOW TIME ZONE;" TimeZone ---------- EST (1 row) postgres@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@sss.pgh.pa.us] Sent: Friday, March 18, 2011 12:47 PM To: Kevin Grittner Cc: JB@BlackSkyTech.com; pgsql-bugs@postgresql.org Subject: Re: [BUGS] TO_CHAR(timestamptz,datetimeformat) wrong after DST change "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes: > "Jonathan Brinkman" <JB@BlackSkyTech.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
pgsql-bugs by date: