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

Re: BUG #4702: refusal to compute date_trunc('week', '0001-12-09 BC');

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Roman Kononov" <kononov(at)ftml(dot)net>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #4702: refusal to compute date_trunc('week', '0001-12-09 BC');
Date: 2009-03-13 23:54:01
Message-ID: 23734.1236988441@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
"Roman Kononov" <kononov(at)ftml(dot)net> writes:
> test=# select date_trunc('week','0001-12-09 BC'::timestamp);
> ERROR:  cannot calculate week number without year information

Hmph, this code is pretty bletcherous.  Sleuthing in the CVS history, it
seems that the error check for "year zero" was put into isoweek2j (then
isoweek2date) at a time when only to_timestamp() used it.  It really
should never have been there at all; it's a syntax check for missing
fields in to_timestamp and has no business interfering with other uses
of the isoweek calculations.  What's more, it can't tell a valid
reference to year zero (a/k/a 1BC) from missing fields, which is the
proximate cause of the complained-of behavior.

It gets worse: it looks to me like the calling code was never right,
because it uses tmfc.year which is just the YY field, ignoring work
that we already did to derive the correct year accounting for CC and BC
fields.

I propose the attached patch ... comments?

			regards, tom lane

Index: src/backend/utils/adt/formatting.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/adt/formatting.c,v
retrieving revision 1.155
diff -c -r1.155 formatting.c
*** src/backend/utils/adt/formatting.c	12 Mar 2009 00:53:25 -0000	1.155
--- src/backend/utils/adt/formatting.c	13 Mar 2009 23:40:45 -0000
***************
*** 3281,3308 ****
  		 * be interpreted as a Gregorian day-of-year, or an ISO week date
  		 * day-of-year.
  		 */
  		if (tmfc.mode == FROM_CHAR_DATE_ISOWEEK)
  		{
  			int			j0;		/* zeroth day of the ISO year, in Julian */
  
! 			j0 = isoweek2j(tmfc.year, 1) - 1;
  
  			j2date(j0 + tmfc.ddd, &tm->tm_year, &tm->tm_mon, &tm->tm_mday);
  		}
  		else
  		{
! 			int		   *y,
! 						i;
  
! 			int			ysum[2][13] = {
  				{31, 59, 90, 120, 151, 181, 212, 243, 273, 304, 334, 365, 0},
  				{31, 60, 91, 121, 152, 182, 213, 244, 274, 305, 335, 366, 0}};
  
- 			if (!tm->tm_year)
- 				ereport(ERROR,
- 						(errcode(ERRCODE_INVALID_DATETIME_FORMAT),
- 						 errmsg("cannot calculate day of year without year information")));
- 
  			y = ysum[isleap(tm->tm_year)];
  
  			for (i = 0; i <= 11; i++)
--- 3281,3309 ----
  		 * be interpreted as a Gregorian day-of-year, or an ISO week date
  		 * day-of-year.
  		 */
+ 
+ 		if (!tm->tm_year && !tmfc.bc)
+ 			ereport(ERROR,
+ 					(errcode(ERRCODE_INVALID_DATETIME_FORMAT),
+ 					 errmsg("cannot calculate day of year without year information")));
+ 
  		if (tmfc.mode == FROM_CHAR_DATE_ISOWEEK)
  		{
  			int			j0;		/* zeroth day of the ISO year, in Julian */
  
! 			j0 = isoweek2j(tm->tm_year, 1) - 1;
  
  			j2date(j0 + tmfc.ddd, &tm->tm_year, &tm->tm_mon, &tm->tm_mday);
  		}
  		else
  		{
! 			const int  *y;
! 			int			i;
  
! 			static const int ysum[2][13] = {
  				{31, 59, 90, 120, 151, 181, 212, 243, 273, 304, 334, 365, 0},
  				{31, 60, 91, 121, 152, 182, 213, 244, 274, 305, 335, 366, 0}};
  
  			y = ysum[isleap(tm->tm_year)];
  
  			for (i = 0; i <= 11; i++)
Index: src/backend/utils/adt/timestamp.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/adt/timestamp.c,v
retrieving revision 1.196
diff -c -r1.196 timestamp.c
*** src/backend/utils/adt/timestamp.c	1 Jan 2009 17:23:50 -0000	1.196
--- src/backend/utils/adt/timestamp.c	13 Mar 2009 23:40:46 -0000
***************
*** 3668,3678 ****
  	int			day0,
  				day4;
  
- 	if (!year)
- 		ereport(ERROR,
- 				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
- 		   errmsg("cannot calculate week number without year information")));
- 
  	/* fourth day of current year */
  	day4 = date2j(year, 1, 4);
  
--- 3668,3673 ----

In response to

pgsql-bugs by date

Next:From: Prem KumarDate: 2009-03-14 07:15:28
Subject: BUG #4703: Client Encoding Mis match
Previous:From: Tom LaneDate: 2009-03-13 21:42:11
Subject: Re: Error in PLpgSQL with SELECT INTO and composite types

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