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)
"Jonathan Brinkman" <JB@BlackSkyTech.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
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@sss.pgh.pa.us]
Sent: Friday, March 18, 2011 11:42 AM
To: JB@BlackSkyTech.com
Cc: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] TO_CHAR(timestamptz,datetimeformat) wrong after DST
change
"Jonathan Brinkman" <JB@BlackSkyTech.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
"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. -Kevin
"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
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
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
On Mon, Mar 21, 2011 at 9:13 AM, Jonathan Brinkman <JB@blackskytech.com> wr= ote: > I understand now that I must use America/New_York for DST to function. = =A0I > 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: SET is a session-local command. You may want to update it in postgresql.conf (and then reload the config using pg_ctl reload). Or you could use ALTER ROLE .. SET or ALTER DATABASE .. SET, if you don't want to change it globally. --=20 Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Thanks,=20 ALTER ROLE postgres SET time zone 'America/New_York'; Fixed the problem! I applied this to my dev server DB anyways, so maybe this will be fixed the next time I migrate to Production. ALTER DATABASE beta_cms_main SET time zone 'America/New_York'; -----Original Message----- From: Robert Haas [mailto:robertmhaas@gmail.com]=20 Sent: Monday, March 21, 2011 11:50 AM To: JB@blackskytech.com Cc: Tom Lane; Kevin Grittner; pgsql-bugs@postgresql.org Subject: Re: [BUGS] TO_CHAR(timestamptz,datetimeformat) wrong after DST change On Mon, Mar 21, 2011 at 9:13 AM, Jonathan Brinkman <JB@blackskytech.com> wrote: > I understand now that I must use America/New_York for DST to function. = =A0I > 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: SET is a session-local command. You may want to update it in postgresql.conf (and then reload the config using pg_ctl reload). Or you could use ALTER ROLE .. SET or ALTER DATABASE .. SET, if you don't want to change it globally. --=20 Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
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@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 > > -- Susanne Ebrecht - 2ndQuadrant PostgreSQL Development, 24x7 Support, Training and Services www.2ndQuadrant.com