Add two new format fields for use with to_char(), to_date() and

to_timestamp():
    - ID for day-of-week
    - IDDD for day-of-year

This makes it possible to convert ISO week dates to and from text
fully represented in either week ('IYYY-IW-ID') or day-of-year
('IYYY-IDDD') format.

I have also added an 'isoyear' field for use with extract / date_part.

Brendan Jurd

1 files changed, 119 insertions, 52 deletions

diff --git a/src/backend/utils/adt/formatting.c b/src/backend/utils/adt/formatting.c
index 80de2f20e4c..eff890c59b3 100644
--- a/src/backend/utils/adt/formatting.c
+++ b/src/backend/utils/adt/formatting.c
@@ -1,7 +1,7 @@
 /* -----------------------------------------------------------------------
  * formatting.c
  *
- * $PostgreSQL: pgsql/src/backend/utils/adt/formatting.c,v 1.124 2007/02/14 05:10:55 momjian Exp $
+ * $PostgreSQL: pgsql/src/backend/utils/adt/formatting.c,v 1.125 2007/02/16 03:39:45 momjian Exp $
  *
  *
  *	 Portions Copyright (c) 1999-2007, PostgreSQL Global Development Group
@@ -379,6 +379,7 @@ typedef struct
 				ddd,
 				mm,
 				ms,
+				iyear,
 				year,
 				bc,
 				iw,
@@ -528,7 +529,7 @@ static KeySuffix DCH_suff[] = {
  * position or -1 if char is not used in the KeyWord. Search example for
  * string "MM":
  *	1)	see in index to index['M' - 32],
- *	2)	take keywords position (enum DCH_MM) from index
+ *	2)	take keywords position (enum DCH_MI) from index
  *	3)	run sequential search in keywords[] from this position
  *
  * ----------
@@ -554,6 +555,8 @@ typedef enum
 	DCH_HH24,
 	DCH_HH12,
 	DCH_HH,
+	DCH_IDDD,
+	DCH_ID,
 	DCH_IW,
 	DCH_IYYY,
 	DCH_IYY,
@@ -598,6 +601,8 @@ typedef enum
 	DCH_hh24,
 	DCH_hh12,
 	DCH_hh,
+	DCH_iddd,
+	DCH_id,
 	DCH_iw,
 	DCH_iyyy,
 	DCH_iyy,
@@ -696,13 +701,15 @@ static const KeyWord DCH_keywords[] = {
 	{"HH24", 4, dch_time, DCH_HH24, TRUE},		/* H */
 	{"HH12", 4, dch_time, DCH_HH12, TRUE},
 	{"HH", 2, dch_time, DCH_HH, TRUE},
-	{"IW", 2, dch_date, DCH_IW, TRUE},	/* I */
+	{"IDDD", 4, dch_date, DCH_IDDD, TRUE},	/* I */
+	{"ID", 2, dch_date, DCH_ID, TRUE},
+	{"IW", 2, dch_date, DCH_IW, TRUE},
 	{"IYYY", 4, dch_date, DCH_IYYY, TRUE},
 	{"IYY", 3, dch_date, DCH_IYY, TRUE},
 	{"IY", 2, dch_date, DCH_IY, TRUE},
 	{"I", 1, dch_date, DCH_I, TRUE},
 	{"J", 1, dch_date, DCH_J, TRUE},	/* J */
-	{"MI", 2, dch_time, DCH_MI, TRUE},
+	{"MI", 2, dch_time, DCH_MI, TRUE},	/* M */
 	{"MM", 2, dch_date, DCH_MM, TRUE},
 	{"MONTH", 5, dch_date, DCH_MONTH, FALSE},
 	{"MON", 3, dch_date, DCH_MON, FALSE},
@@ -740,7 +747,9 @@ static const KeyWord DCH_keywords[] = {
 	{"hh24", 4, dch_time, DCH_HH24, TRUE},		/* h */
 	{"hh12", 4, dch_time, DCH_HH12, TRUE},
 	{"hh", 2, dch_time, DCH_HH, TRUE},
-	{"iw", 2, dch_date, DCH_IW, TRUE},	/* i */
+	{"iddd", 4, dch_date, DCH_IDDD, TRUE},	/* i */
+	{"id", 2, dch_date, DCH_ID, TRUE},
+	{"iw", 2, dch_date, DCH_IW, TRUE},
 	{"iyyy", 4, dch_date, DCH_IYYY, TRUE},
 	{"iyy", 3, dch_date, DCH_IYY, TRUE},
 	{"iy", 2, dch_date, DCH_IY, TRUE},
@@ -830,10 +839,10 @@ static const int DCH_index[KeyWord_INDEX_SIZE] = {
 	-1, -1, -1, -1, -1, -1, -1, -1, -1, -1,
 	-1, -1, -1, -1, -1, -1, -1, -1, -1, -1,
 	-1, -1, -1, -1, -1, DCH_A_D, DCH_B_C, DCH_CC, DCH_DAY, -1,
-	DCH_FX, -1, DCH_HH24, DCH_IW, DCH_J, -1, -1, DCH_MI, -1, -1,
+	DCH_FX, -1, DCH_HH24, DCH_IDDD, DCH_J, -1, -1, DCH_MI, -1, -1,
 	DCH_P_M, DCH_Q, DCH_RM, DCH_SSSS, DCH_TZ, DCH_US, -1, DCH_WW, -1, DCH_Y_YYY,
 	-1, -1, -1, -1, -1, -1, -1, DCH_a_d, DCH_b_c, DCH_cc,
-	DCH_day, -1, DCH_fx, -1, DCH_hh24, DCH_iw, DCH_j, -1, -1, DCH_mi,
+	DCH_day, -1, DCH_fx, -1, DCH_hh24, DCH_iddd, DCH_j, -1, -1, DCH_mi,
 	-1, -1, DCH_p_m, DCH_q, DCH_rm, DCH_ssss, DCH_tz, DCH_us, -1, DCH_ww,
 	-1, DCH_y_yyy, -1, -1, -1, -1
 
@@ -2429,9 +2438,13 @@ dch_date(int arg, char *inout, int suf, bool is_to_char, bool is_interval,
 			return strlen(p_inout);
 
 		case DCH_DDD:
+		case DCH_IDDD:
 			if (is_to_char)
 			{
-				sprintf(inout, "%0*d", S_FM(suf) ? 0 : 3, tm->tm_yday);
+				sprintf(inout, "%0*d", S_FM(suf) ? 0 : 3, 
+					(arg == DCH_DDD) ? 
+					tm->tm_yday :
+					date2isoyearday(tm->tm_year, tm->tm_mon, tm->tm_mday));
 				if (S_THth(suf))
 					str_numth(p_inout, inout, S_TH_TYPE(suf));
 				return strlen(p_inout);
@@ -2473,10 +2486,14 @@ dch_date(int arg, char *inout, int suf, bool is_to_char, bool is_interval,
 			}
 			break;
 		case DCH_D:
+		case DCH_ID:
 			INVALID_FOR_INTERVAL;
 			if (is_to_char)
 			{
-				sprintf(inout, "%d", tm->tm_wday + 1);
+				if (arg == DCH_D)
+					sprintf(inout, "%d", tm->tm_wday + 1);
+				else
+					sprintf(inout, "%d", (tm->tm_wday == 0) ? 7 : tm->tm_wday);
 				if (S_THth(suf))
 					str_numth(p_inout, inout, S_TH_TYPE(suf));
 				return strlen(p_inout);
@@ -2484,7 +2501,8 @@ dch_date(int arg, char *inout, int suf, bool is_to_char, bool is_interval,
 			else
 			{
 				sscanf(inout, "%1d", &tmfc->d);
-				tmfc->d--;
+				if (arg == DCH_D)
+					tmfc->d--;
 				return strspace_len(inout) + 1 + SKIP_THth(suf);
 			}
 			break;
@@ -2625,15 +2643,18 @@ dch_date(int arg, char *inout, int suf, bool is_to_char, bool is_interval,
 			}
 			else
 			{
+				int *field;
+				field = (arg == DCH_YYYY) ? &tmfc->year : &tmfc->iyear;
+
 				if (S_FM(suf) || is_next_separator(node))
 				{
-					sscanf(inout, "%d", &tmfc->year);
+					sscanf(inout, "%d", field);
 					tmfc->yysz = 4;
 					return strdigits_len(inout) + SKIP_THth(suf);
 				}
 				else
 				{
-					sscanf(inout, "%04d", &tmfc->year);
+					sscanf(inout, "%04d", field);
 					tmfc->yysz = 4;
 					return strspace_len(inout) + 4 + SKIP_THth(suf);
 				}
@@ -2657,16 +2678,19 @@ dch_date(int arg, char *inout, int suf, bool is_to_char, bool is_interval,
 			}
 			else
 			{
-				sscanf(inout, "%03d", &tmfc->year);
+				int *field;
+				field = (arg == DCH_YYY) ? &tmfc->year : &tmfc->iyear;
+
+				sscanf(inout, "%03d", field);
 
 				/*
 				 * 3-digit year: '100' ... '999' = 1100 ... 1999 '000' ...
 				 * '099' = 2000 ... 2099
 				 */
-				if (tmfc->year >= 100)
-					tmfc->year += 1000;
+				if (*field >= 100)
+					*field += 1000;
 				else
-					tmfc->year += 2000;
+					*field += 2000;
 				tmfc->yysz = 3;
 				return strspace_len(inout) + 3 + SKIP_THth(suf);
 			}
@@ -2689,16 +2713,19 @@ dch_date(int arg, char *inout, int suf, bool is_to_char, bool is_interval,
 			}
 			else
 			{
-				sscanf(inout, "%02d", &tmfc->year);
+				int *field;
+				field = (arg == DCH_YY) ? &tmfc->year : &tmfc->iyear;
+
+				sscanf(inout, "%02d", field);
 
 				/*
 				 * 2-digit year: '00' ... '69'	= 2000 ... 2069 '70' ... '99'
 				 * = 1970 ... 1999
 				 */
-				if (tmfc->year < 70)
-					tmfc->year += 2000;
+				if (*field < 70)
+					*field += 2000;
 				else
-					tmfc->year += 1900;
+					*field += 1900;
 				tmfc->yysz = 2;
 				return strspace_len(inout) + 2 + SKIP_THth(suf);
 			}
@@ -2721,12 +2748,15 @@ dch_date(int arg, char *inout, int suf, bool is_to_char, bool is_interval,
 			}
 			else
 			{
-				sscanf(inout, "%1d", &tmfc->year);
+				int *field;
+				field = (arg == DCH_Y) ? &tmfc->year : &tmfc->iyear;
+
+				sscanf(inout, "%1d", field);
 
 				/*
 				 * 1-digit year: always +2000
 				 */
-				tmfc->year += 2000;
+				*field += 2000;
 				tmfc->yysz = 1;
 				return strspace_len(inout) + 1 + SKIP_THth(suf);
 			}
@@ -3297,7 +3327,8 @@ do_to_timestamp(text *date_txt, text *fmt,
 {
 	FormatNode *format;
 	TmFromChar	tmfc;
-	int			fmt_len;
+	int			fmt_len,
+				year;
 
 	ZERO_tm(tm);
 	*fsec = 0;
@@ -3447,7 +3478,13 @@ do_to_timestamp(text *date_txt, text *fmt,
 			break;
 	}
 
-	if (tmfc.year)
+	/*
+	 * Only one year value is used.  If iyear (the ISO year) is defined, it takes precedence.  
+	 * Otherwise year (the Gregorian year) is used.
+	 */
+	year = (tmfc.iyear) ? tmfc.iyear : tmfc.year;
+
+	if (year)
 	{
 		/*
 		 * If CC and YY (or Y) are provided, use YY as 2 low-order digits
@@ -3458,14 +3495,14 @@ do_to_timestamp(text *date_txt, text *fmt,
 		 */
 		if (tmfc.cc && tmfc.yysz <= 2)
 		{
-			tm->tm_year = tmfc.year % 100;
+			tm->tm_year = year % 100;
 			if (tm->tm_year)
 				tm->tm_year += (tmfc.cc - 1) * 100;
 			else
 				tm->tm_year = tmfc.cc * 100;
 		}
 		else
-			tm->tm_year = tmfc.year;
+			tm->tm_year = year;
 	}
 	else if (tmfc.cc)			/* use first year of century */
 		tm->tm_year = (tmfc.cc - 1) * 100 + 1;
@@ -3485,48 +3522,78 @@ do_to_timestamp(text *date_txt, text *fmt,
 		j2date(tmfc.j, &tm->tm_year, &tm->tm_mon, &tm->tm_mday);
 
 	if (tmfc.iw)
-		isoweek2date(tmfc.iw, &tm->tm_year, &tm->tm_mon, &tm->tm_mday);
+	{
+		/* 
+		 * Since the user has employed the IW field, it is assumed that the value in tmfc.d 
+		 * is in ISO day-of-week form (1 = Monday), as set by the ID field.  Mixing IW and D 
+		 * will yield weird results.
+		 *
+		 * tmfc.iyear must have been set (e.g., with IYYY) for this to work properly (an ISO week
+		 * without an ISO year is meaningless).
+		 *
+		 * If tmfc.d is not set, then the date is left at the beginning of the ISO week (Monday).
+		 */
+		if (tmfc.d)
+		{
+			isoweekdate2date(tmfc.iw, tmfc.d, &tm->tm_year, &tm->tm_mon, &tm->tm_mday);
+		}
+		else
+			isoweek2date(tmfc.iw, &tm->tm_year, &tm->tm_mon, &tm->tm_mday);
+	}
+
 
 	if (tmfc.d)
 		tm->tm_wday = tmfc.d;
 	if (tmfc.dd)
 		tm->tm_mday = tmfc.dd;
-	if (tmfc.ddd)
+	if (tmfc.ddd && !tmfc.iyear)
 		tm->tm_yday = tmfc.ddd;
 	if (tmfc.mm)
 		tm->tm_mon = tmfc.mm;
 
-	/*
-	 * we don't ignore DDD
-	 */
 	if (tmfc.ddd && (tm->tm_mon <= 1 || tm->tm_mday <= 1))
 	{
-		/* count mday and mon from yday */
-		int		   *y,
-					i;
+		/*
+		 * If the iyear field is set, the value of ddd is taken to be an ISO day-of-year.
+		 * Otherwise, it is a Gregorian day-of-year.
+		 * Either way, since the month and day fields have not been set by some other means,
+		 * the value of ddd will be used to compute them.
+		 */
+		if (tmfc.iyear)
+		{
+			int j0;		// zeroth day of the ISO year, in Julian
+			j0 = isoweek2j(tmfc.iyear, 1) - 1;
 
-		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}};
+			j2date(j0 + tmfc.ddd, &tm->tm_year, &tm->tm_mon, &tm->tm_mday);
+		}
+		else
+		{
+			int		   *y,
+						i;
 
-		if (!tm->tm_year)
-			ereport(ERROR,
-					(errcode(ERRCODE_INVALID_DATETIME_FORMAT),
-			errmsg("cannot calculate day of year without year information")));
+			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)];
+			if (!tm->tm_year)
+				ereport(ERROR,
+						(errcode(ERRCODE_INVALID_DATETIME_FORMAT),
+				errmsg("cannot calculate day of year without year information")));
 
-		for (i = 0; i <= 11; i++)
-		{
-			if (tm->tm_yday < y[i])
-				break;
-		}
-		if (tm->tm_mon <= 1)
-			tm->tm_mon = i + 1;
+			y = ysum[isleap(tm->tm_year)];
 
-		if (tm->tm_mday <= 1)
-			tm->tm_mday = i == 0 ? tm->tm_yday :
-				tm->tm_yday - y[i - 1];
+			for (i = 0; i <= 11; i++)
+			{
+				if (tm->tm_yday < y[i])
+					break;
+			}
+			if (tm->tm_mon <= 1)
+				tm->tm_mon = i + 1;
+
+			if (tm->tm_mday <= 1)
+				tm->tm_mday = i == 0 ? tm->tm_yday :
+					tm->tm_yday - y[i - 1];
+		}
 	}
 
 #ifdef HAVE_INT64_TIMESTAMP