Skip site navigation (1) Skip section navigation (2)

Strange behavior with to_char and dates

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 (view raw or flat)
Thread:
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


Responses

pgsql-bugs by date

Next:From: Heikki LinnakangasDate: 2009-01-09 14:46:54
Subject: Re: Strange behavior with to_char and dates
Previous:From: Magnus HaganderDate: 2009-01-09 14:18:07
Subject: Re: Fwd: [BUGS] BUG #4607: Site bug

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group