FAQ
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)

Search Discussions

  • Tom Lane at Mar 18, 2011 at 3:42 pm

    "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 at Mar 18, 2011 at 4:07 pm
    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
    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
  • Kevin Grittner at Mar 18, 2011 at 4:34 pm

    "Jonathan Brinkman" wrote:

    I guess EST is not DST-friendly?
    EST stands for Eastern *Standard* Time, which is explicitly *not*
    under Daylight Saving Time.

    -Kevin
  • Tom Lane at Mar 18, 2011 at 4:47 pm

    "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
    "Jonathan Brinkman" 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
  • Jonathan Brinkman at Mar 21, 2011 at 1:19 pm
    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
    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" 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
  • Robert Haas at Mar 21, 2011 at 3:51 pm

    On Mon, Mar 21, 2011 at 9:13 AM, Jonathan Brinkman wrote:
    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:
    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.

    --
    Robert Haas
    EnterpriseDB: http://www.enterprisedb.com
    The Enterprise PostgreSQL Company
  • Jonathan Brinkman at Mar 22, 2011 at 12:56 pm
    Thanks,
    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
    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 wrote:
    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:
    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.

    --
    Robert Haas
    EnterpriseDB: http://www.enterprisedb.com
    The Enterprise PostgreSQL Company
  • Jonathan Brinkman at Mar 21, 2011 at 1:30 pm
    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
    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
    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" 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 at Mar 23, 2011 at 7:34 pm
    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
    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
    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"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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-bugs @
categoriespostgresql
postedMar 18, '11 at 2:55p
activeMar 23, '11 at 7:34p
posts10
users5
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase