Lists: | pgsql-bugs |
---|
From: | "Denis Percevault" <d(dot)percevault(at)pnsconcept(dot)fr> |
---|---|
To: | <pgsql-bugs(at)postgresql(dot)org> |
Subject: | Strange behavior with to_char and dates |
Date: | 2009-01-09 14:18:12 |
Message-ID: | 20090109141812.A2BFAD48109@smtp5-g21.free.fr |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
Hello,
To day we are the 9th of January 2009.
The following request preformed on a version 8.0.8 or on 8.3.5 gives the same strange result.
As you can see below,
the request "to_char((current_date - 11), 'DD MM IYYY')" gives "29 12 2009" instead of "29 12 2008". With 12 or with 8 the result is
good.
EXTRACT is a good workaround.
What did I wrong?
Is my request bad or is it a bug?
Best regards,
select
to_char(current_date, 'DD MM IYYY') as good_curdate,
current_date - 10 as good_cur_10,
to_char((current_date - 10), 'DD MM IYYY') as bad_date1_10,
to_char((date(now())-10), 'DD MM IYYY') as bad_date2_10,
to_char((current_date - 13), 'DD MM IYYY') as good_date_13,
to_char((current_date - 12), 'DD MM IYYY') as good_date_12,
to_char((current_date - 11), 'DD MM IYYY') as bad_date_11,
to_char((current_date - 10), 'DD MM IYYY') as bad_date_10,
to_char((current_date - 9), 'DD MM IYYY') as bad_date_9,
to_char((current_date - 8), 'DD MM IYYY') as good_date_8,
to_char((current_date - 7), 'DD MM IYYY') as good_date_7,
EXTRACT(day FROM (date(now())-10) ) as good_day_10,
EXTRACT(month FROM (date(now())-10) ) as good_month_10,
EXTRACT(YEAR FROM (date(now())-10) ) as good_year_10;
good_curdate : "09 01 2009"
good_cur_10 : "2008-12-30"
bad_date1_10 : "30 12 2009"
bad_date2_10 : "30 12 2009"
good_date_13 : "27 12 2008"
good_date_12 : "28 12 2008"
bad_date_11 : "29 12 2009"
bad_date_10 : "30 12 2009"
bad_date_9 : "31 12 2009"
good_date_8 : "01 01 2009"
good_date_7 : "02 01 2009"
good_day_10 : 30
good_month_10 : 12
good_year_10 : 2008
Denis Percevault
d(dot)percevault(at)pnsconcept(dot)fr
From: | Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com> |
---|---|
To: | d(dot)percevault(at)pnsconcept(dot)fr |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: Strange behavior with to_char and dates |
Date: | 2009-01-09 14:46:54 |
Message-ID: | 4967635E.1000306@enterprisedb.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
Denis Percevault wrote:
> As you can see below,
> the request "to_char((current_date - 11), 'DD MM IYYY')" gives "29 12 2009" instead of "29 12 2008". With 12 or with 8 the result is
> good.
What you want is 'YYYY' instead of 'IYYY'. 'IYYY' is intended to be used
together with 'IW' and 'ID', to give a so-called "ISO week date". See
manual for more information, particularly this note:
> Attempting to construct a date using a mixture of ISO week and Gregorian date fields is nonsensical, and could yield unexpected results. In the context of an ISO year, the concept of a 'month' or 'day of month' has no meaning. In the context of a Gregorian year, the ISO week has no meaning. Users should take care to keep Gregorian and ISO date specifications separate.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com