Re: [BUGS] BUG #2977: dow doesn't conform to ISO-8601

Updated version applied.  I reduced the numering changes for the macros.

There was also documentation text for "dow" and a few others that said
"(for timestamp values only)", but in fact the field worked
for "timestamptz" and "date" too, so I removed the mentions.  If people
get confused, I will come up with new wording, like "doesn't work for
interval or time", which I think is pretty obvious.  I remember people
got confused in the past about this, so maybe we still need something.

---------------------------------------------------------------------------

Bruce Momjian wrote:
> 
> I have implemented 'isodow' with the attached patch.
> 
> ---------------------------------------------------------------------------
> 
> Peter Eisentraut wrote:
> > Am Mittwoch, 7. Februar 2007 11:24 schrieb Adriaan van Os:
> > > Section 9.9.1 of the Postgres docs
> > > 
> > > mentions that the "week" subfield of the date_part function conforms to the
> > > ISO-8601 standard.
> > 
> > I see no such claim there.
> > 
> > > You can not, in the same function, ignore ISO-8601 for one subfield and
> > > follow it in another.
> > 
> > I think we have pretty well shown that we can.
> > 
> > > Besides, if in the same week Sunday comes before Monday, how can the result
> > > of the "week" and "dow" fields conform to each other ?
> > 
> > They don't.
> > 
> > > If "dow" can not be changed for reasons of backward compatibility, I
> > > suggest a new subfield "dayofweek" that does conform to the standard.
> > 
> > That might be reasonable. (Or maybe "isodow".)
> > 
> > -- 
> > Peter Eisentraut
> > http://developer.postgresql.org/~petere/
> > 
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: explain analyze is your friend
> 
> -- 
>   Bruce Momjian            http://momjian.us
>   EnterpriseDB                               http://www.enterprisedb.com
> 
>   + If your life is a hard drive, Christ can be your backup. +


> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings

-- 
  Bruce Momjian            http://momjian.us
  EnterpriseDB                               http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +
Index: doc/src/sgml/func.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/func.sgml,v
retrieving revision 1.361
diff -c -c -r1.361 func.sgml
*** doc/src/sgml/func.sgml	16 Feb 2007 07:46:54 -0000	1.361
--- doc/src/sgml/func.sgml	19 Feb 2007 17:38:23 -0000
***************
*** 5732,5739 ****
        dow
        
         
!         The day of the week (0 - 6; Sunday is 0) (for
!         timestamp values only)
         
  
  
--- 5732,5739 ----
        dow
        
         
!         The day of the week as Sunday(0</>) to
!         Saturday(6</>)
         
  
  
***************
*** 5741,5747 ****
  Result: 5
  
         
!         Note that extract's day of the week numbering is 
          different from that of the to_char function.
         
  
--- 5741,5747 ----
  Result: 5
  
         
!         Note that extract's day of the week numbering is
          different from that of the to_char function.
         
  
***************
*** 5752,5758 ****
        doy
        
         
!         The day of the year (1 - 365/366) (for timestamp values only)
         
  
  
--- 5752,5758 ----
        doy
        
         
!         The day of the year (1 - 365/366)
         
  
  
***************
*** 5806,5811 ****
--- 5806,5831 ----
       
  
       
+       isodow
+       
+        
+         The day of the week as Monday(1</>) to
+         Sunday(7</>)
+        
+ 
+ 
+ SELECT EXTRACT(ISODOW FROM TIMESTAMP '2001-02-18 20:38:40');
+ Result: 7
+ 
+        
+         This is identical to dow</> except for Sunday.  This
+         matches the ISO</> 8601 day of the week numbering.
+        
+ 
+       
+      
+ 
+      
        isoyear
        
         
***************
*** 5923,5930 ****
        quarter
        
         
!         The quarter of the year (1 - 4) that the day is in (for
!         timestamp values only)
         
  
  
--- 5943,5949 ----
        quarter
        
         
!         The quarter of the year (1 - 4) that the day is in
         
  
  
***************
*** 5989,5995 ****
          (ISO 8601), the first week of a year
          contains January 4 of that year.  (The ISO-8601
          week starts on Monday.)  In other words, the first Thursday of
!         a year is in week 1 of that year. (for timestamp values only)
         
         
          Because of this, it is possible for early January dates to be part of the 
--- 6008,6014 ----
          (ISO 8601), the first week of a year
          contains January 4 of that year.  (The ISO-8601
          week starts on Monday.)  In other words, the first Thursday of
!         a year is in week 1 of that year.
         
         
          Because of this, it is possible for early January dates to be part of the 
Index: src/backend/utils/adt/datetime.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/adt/datetime.c,v
retrieving revision 1.176
diff -c -c -r1.176 datetime.c
*** src/backend/utils/adt/datetime.c	16 Feb 2007 03:39:45 -0000	1.176
--- src/backend/utils/adt/datetime.c	19 Feb 2007 17:38:25 -0000
***************
*** 125,130 ****
--- 125,131 ----
  	{"h", UNITS, DTK_HOUR},		/* "hour" */
  	{LATE, RESERV, DTK_LATE},	/* "infinity" reserved for "late time" */
  	{INVALID, RESERV, DTK_INVALID},		/* "invalid" reserved for bad time */
+ 	{"isodow", RESERV, DTK_ISODOW},		/* ISO day of week, Sunday == 7 */
  	{"isoyear", UNITS, DTK_ISOYEAR},	/* year in terms of the ISO week date */
  	{"j", UNITS, DTK_JULIAN},
  	{"jan", MONTH, 1},
Index: src/backend/utils/adt/timestamp.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/adt/timestamp.c,v
retrieving revision 1.172
diff -c -c -r1.172 timestamp.c
*** src/backend/utils/adt/timestamp.c	16 Feb 2007 03:39:45 -0000	1.172
--- src/backend/utils/adt/timestamp.c	19 Feb 2007 17:38:26 -0000
***************
*** 4112,4122 ****
--- 4112,4125 ----
  					break;
  				}
  			case DTK_DOW:
+ 			case DTK_ISODOW:
  				if (timestamp2tm(timestamp, NULL, tm, &fsec, NULL, NULL) != 0)
  					ereport(ERROR,
  							(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
  							 errmsg("timestamp out of range")));
  				result = j2day(date2j(tm->tm_year, tm->tm_mon, tm->tm_mday));
+ 				if (val == DTK_ISODOW && result == 0)
+ 					result = 7;
  				break;
  
  			case DTK_DOY:
***************
*** 4322,4332 ****
--- 4325,4338 ----
  				break;
  
  			case DTK_DOW:
+ 			case DTK_ISODOW:
  				if (timestamp2tm(timestamp, &tz, tm, &fsec, &tzn, NULL) != 0)
  					ereport(ERROR,
  							(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
  							 errmsg("timestamp out of range")));
  				result = j2day(date2j(tm->tm_year, tm->tm_mon, tm->tm_mday));
+ 				if (val == DTK_ISODOW && result == 0)
+ 					result = 7;
  				break;
  
  			case DTK_DOY:
Index: src/include/utils/datetime.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/utils/datetime.h,v
retrieving revision 1.64
diff -c -c -r1.64 datetime.h
*** src/include/utils/datetime.h	16 Feb 2007 03:39:45 -0000	1.64
--- src/include/utils/datetime.h	19 Feb 2007 17:38:27 -0000
***************
*** 166,171 ****
--- 166,172 ----
  #define DTK_TZ_HOUR		34
  #define DTK_TZ_MINUTE	35
  #define DTK_ISOYEAR		36
+ #define DTK_ISODOW		37
  
  
  /*
Index: src/interfaces/ecpg/pgtypeslib/dt.h
===================================================================
RCS file: /cvsroot/pgsql/src/interfaces/ecpg/pgtypeslib/dt.h,v
retrieving revision 1.35
diff -c -c -r1.35 dt.h
*** src/interfaces/ecpg/pgtypeslib/dt.h	16 Feb 2007 03:39:45 -0000	1.35
--- src/interfaces/ecpg/pgtypeslib/dt.h	19 Feb 2007 17:38:27 -0000
***************
*** 158,163 ****
--- 158,164 ----
  #define DTK_TZ_HOUR		34
  #define DTK_TZ_MINUTE	35
  #define DTK_ISOYEAR		36
+ #define DTK_ISODOW		37
  
  
  /*
Index: src/interfaces/ecpg/pgtypeslib/dt_common.c
===================================================================
RCS file: /cvsroot/pgsql/src/interfaces/ecpg/pgtypeslib/dt_common.c,v
retrieving revision 1.36
diff -c -c -r1.36 dt_common.c
*** src/interfaces/ecpg/pgtypeslib/dt_common.c	26 Sep 2006 07:56:56 -0000	1.36
--- src/interfaces/ecpg/pgtypeslib/dt_common.c	19 Feb 2007 17:38:28 -0000
***************
*** 214,219 ****
--- 214,220 ----
  	{"irkst", DTZ, POS(36)},	/* Irkutsk Summer Time */
  	{"irkt", TZ, POS(32)},		/* Irkutsk Time */
  	{"irt", TZ, POS(14)},		/* Iran Time */
+ 	{"isodow", RESERV, DTK_ISODOW},	/* ISO day of week, Sunday == 7 */
  #if 0
  	isst
  #endif