Re: Created feature for to_date() conversion using patterns 'YYYY-WW', 'YYYY-WW-D', 'YYYY-MM-W' and 'YYYY-MM-W-D'

From 4e35bd88bef1916e7d11ad0776b3075e3183f7d0 Mon Sep 17 00:00:00 2001
From: Mark Lorenz <[email protected]>
Date: Fri, 20 Dec 2019 14:30:41 +0100
Subject: [PATCH 1/3] change to_date()/to_timestamp() behaviour with
 'YYYY-WW-D' pattern

Currently, the D part is ignored at non-ISO week pattern. Now the D
pattern works as well.

Added regression tests
---
 src/backend/utils/adt/formatting.c     | 44 +++++++++++---
 src/backend/utils/adt/timestamp.c      | 84 ++++++++++++++++++++++++++
 src/include/utils/timestamp.h          |  6 ++
 src/test/regress/expected/horology.out | 26 +++++++-
 src/test/regress/sql/horology.sql      |  4 ++
 5 files changed, 156 insertions(+), 8 deletions(-)

diff --git a/src/backend/utils/adt/formatting.c b/src/backend/utils/adt/formatting.c
index 8fcbc22..603c51c 100644
--- a/src/backend/utils/adt/formatting.c
+++ b/src/backend/utils/adt/formatting.c
@@ -4494,21 +4494,51 @@ do_to_timestamp(text *date_txt, text *fmt, bool std,
 			fmask |= DTK_DATE_M;
 		}
 		else
-			tmfc.ddd = (tmfc.ww - 1) * 7 + 1;
+		{
+			/*
+			 * If tmfc.d is not set, then the date is left at the beginning of
+			 * the week (Sunday).
+			 */
+			if (tmfc.d)
+				weekdate2date(tmfc.ww, tmfc.d, &tm->tm_year, &tm->tm_mon, &tm->tm_mday);
+			else
+				week2date(tmfc.ww, &tm->tm_year, &tm->tm_mon, &tm->tm_mday);
+			fmask |= DTK_DATE_M;
+		}
+	}
+
+	if (tmfc.mm)
+	{
+		tm->tm_mon = tmfc.mm;
+		fmask |= DTK_M(MONTH);
 	}
 
 	if (tmfc.w)
-		tmfc.dd = (tmfc.w - 1) * 7 + 1;
+	{
+		/* if tmfc.mm is set, the date can be calculated */
+		if (tmfc.mm)
+		{
+			/*
+			 * If tmfc.d is not set, then the date is left at the beginning of
+			 * the week (Sunday).
+			 */
+			if (tmfc.d)
+				monthweekdate2date(tmfc.mm, tmfc.w, tmfc.d, &tm->tm_year, &tm->tm_mon, &tm->tm_mday);
+			else
+				monthweek2date(tmfc.mm, tmfc.w, &tm->tm_year, &tm->tm_mon, &tm->tm_mday);
+
+			fmask |= DTK_DATE_M;
+			tmfc.dd = tm->tm_mday;
+		}
+		else
+			tmfc.dd = (tmfc.w - 1) * 7 + 1;
+	}
+
 	if (tmfc.dd)
 	{
 		tm->tm_mday = tmfc.dd;
 		fmask |= DTK_M(DAY);
 	}
-	if (tmfc.mm)
-	{
-		tm->tm_mon = tmfc.mm;
-		fmask |= DTK_M(MONTH);
-	}
 
 	if (tmfc.ddd && (tm->tm_mon <= 1 || tm->tm_mday <= 1))
 	{
diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/timestamp.c
index 945b8f8..3e2f499 100644
--- a/src/backend/utils/adt/timestamp.c
+++ b/src/backend/utils/adt/timestamp.c
@@ -4264,6 +4264,90 @@ interval_trunc(PG_FUNCTION_ARGS)
 	PG_RETURN_INTERVAL_P(result);
 }
 
+/* monthweek2j()
+ *
+ *	Return the Julian day which corresponds to the first day (Sunday) of the given month/year and week.
+ *	Julian days are used to convert between ISO week dates and Gregorian dates.
+ */
+int
+monthweek2j(int year, int month, int week)
+{
+	int			day0,
+				day1;
+
+	/* first day of given month */
+	day1 = date2j(year, month, 1);
+
+	// day0 == offset to first day of week (Sunday)
+	day0 = j2day(day1);
+
+	return ((week - 1) * 7) + (day1 - day0);
+}
+
+/* monthweek2date()
+ * Convert week of month and year number to date.
+ */
+void
+monthweek2date(int month, int wom, int *year, int *mon, int *mday)
+{
+	j2date(monthweek2j(*year, month, wom), year, mon, mday);
+}
+
+/* monthweek2date()
+ *
+ *	Convert a week of month date (year, month, week of month) into a Gregorian date.
+ *	Gregorian day of week sent so weekday strings can be supplied.
+ *	Populates year, mon, and mday with the correct Gregorian values.
+ */
+void
+monthweekdate2date(int month, int wom, int wday, int *year, int *mon, int *mday)
+{
+	int 		jday;
+
+	jday = monthweek2j(*year, month, wom);
+	jday += wday - 1;
+
+	j2date(jday, year, mon, mday);
+}
+
+/* week2j()
+ *
+ *	Return the Julian day which corresponds to the first day (Sunday) of the given year and week.
+ *	Julian days are used to convert between ISO week dates and Gregorian dates.
+ */
+int
+week2j(int year, int week)
+{
+	/* calculating the Julian Day from first month of current year */
+	return monthweek2j(year, 1, week);
+}
+
+/* week2date()
+ * Convert week of year number to date.
+ */
+void
+week2date(int woy, int *year, int *mon, int *mday)
+{
+	j2date(week2j(*year, woy), year, mon, mday);
+}
+
+/* weekdate2date()
+ *
+ *	Convert a week date (year, week) into a Gregorian date.
+ *	Gregorian day of week sent so weekday strings can be supplied.
+ *	Populates year, mon, and mday with the correct Gregorian values.
+ */
+void
+weekdate2date(int woy, int wday, int *year, int *mon, int *mday)
+{
+	int			jday;
+
+	jday = week2j(*year, woy);
+	jday += wday - 1;
+
+	j2date(jday, year, mon, mday);
+}
+
 /* isoweek2j()
  *
  *	Return the Julian day which corresponds to the first day (Monday) of the given ISO 8601 year and week.
diff --git a/src/include/utils/timestamp.h b/src/include/utils/timestamp.h
index 7652b41..4c417fc 100644
--- a/src/include/utils/timestamp.h
+++ b/src/include/utils/timestamp.h
@@ -100,6 +100,12 @@ extern int	timestamp_cmp_internal(Timestamp dt1, Timestamp dt2);
 extern TimestampTz timestamp2timestamptz_opt_overflow(Timestamp timestamp,
 													  int *overflow);
 
+extern int	monthweek2j(int year, int month, int week);
+extern void	monthweek2date(int month, int wom, int *year, int *mon, int *mday);
+extern void	monthweekdate2date(int month, int wom, int wday, int *year, int *mon, int *mday);
+extern int	week2j(int year, int week);
+extern void	week2date(int woy, int *year, int *mon, int *mday);
+extern void	weekdate2date(int woy, int wday, int *year, int *mon, int *mday);
 extern int	isoweek2j(int year, int week);
 extern void isoweek2date(int woy, int *year, int *mon, int *mday);
 extern void isoweekdate2date(int isoweek, int wday, int *year, int *mon, int *mday);
diff --git a/src/test/regress/expected/horology.out b/src/test/regress/expected/horology.out
index 6b53876..d0dcc2a 100644
--- a/src/test/regress/expected/horology.out
+++ b/src/test/regress/expected/horology.out
@@ -2681,7 +2681,31 @@ SELECT to_timestamp('995-1116', 'YYY-MMDD');
 SELECT to_timestamp('2005426', 'YYYYWWD');
          to_timestamp         
 ------------------------------
- Sat Oct 15 00:00:00 2005 PDT
+ Fri Oct 14 00:00:00 2005 PDT
+(1 row)
+
+SELECT to_timestamp('2019011', 'YYYYWWD');
+         to_timestamp         
+------------------------------
+ Sun Dec 30 00:00:00 2018 PST
+(1 row)
+
+SELECT to_timestamp('2019013', 'YYYYWWD');
+         to_timestamp         
+------------------------------
+ Tue Jan 01 00:00:00 2019 PST
+(1 row)
+
+SELECT to_timestamp('2019017', 'YYYYWWD');
+         to_timestamp         
+------------------------------
+ Sat Jan 05 00:00:00 2019 PST
+(1 row)
+
+SELECT to_timestamp('2019021', 'YYYYWWD');
+         to_timestamp         
+------------------------------
+ Sun Jan 06 00:00:00 2019 PST
 (1 row)
 
 SELECT to_timestamp('2005300', 'YYYYDDD');
diff --git a/src/test/regress/sql/horology.sql b/src/test/regress/sql/horology.sql
index f7a9da1..2ae64af 100644
--- a/src/test/regress/sql/horology.sql
+++ b/src/test/regress/sql/horology.sql
@@ -372,6 +372,10 @@ SELECT to_timestamp('95-1116', 'YY-MMDD');
 SELECT to_timestamp('995-1116', 'YYY-MMDD');
 
 SELECT to_timestamp('2005426', 'YYYYWWD');
+SELECT to_timestamp('2019011', 'YYYYWWD');
+SELECT to_timestamp('2019013', 'YYYYWWD');
+SELECT to_timestamp('2019017', 'YYYYWWD');
+SELECT to_timestamp('2019021', 'YYYYWWD');
 
 SELECT to_timestamp('2005300', 'YYYYDDD');
 
-- 
2.20.1


From fd458bc25720705dccd4af6d89d19b05c04add43 Mon Sep 17 00:00:00 2001
From: Mark Lorenz <[email protected]>
Date: Fri, 10 Jan 2020 13:01:17 +0100
Subject: [PATCH 2/3] Change documentation to meet the new behaviour

---
 doc/src/sgml/func.sgml | 6 ++----
 1 file changed, 2 insertions(+), 4 deletions(-)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 4b42f12..9877877 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -6750,10 +6750,8 @@ SELECT regexp_match('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}');
      <listitem>
       <para>
        In <function>to_timestamp</function> and <function>to_date</function>,
-       weekday names or numbers (<literal>DAY</literal>, <literal>D</literal>,
-       and related field types) are accepted but are ignored for purposes of
-       computing the result.  The same is true for quarter
-       (<literal>Q</literal>) fields.
+       quarter fields (<literal>Q</literal>) are accepted but are ignored for purposes of
+       computing the result.
       </para>
      </listitem>
 
-- 
2.20.1


From 0e928115e0833b1d04c76096a9571e2c7654db37 Mon Sep 17 00:00:00 2001
From: Mark Lorenz <[email protected]>
Date: Fri, 10 Jan 2020 13:01:36 +0100
Subject: [PATCH 3/3] Added Tests for DAY, DY and dy fields

---
 src/test/regress/expected/horology.out | 24 ++++++++++++++++++++++++
 src/test/regress/sql/horology.sql      |  4 ++++
 2 files changed, 28 insertions(+)

diff --git a/src/test/regress/expected/horology.out b/src/test/regress/expected/horology.out
index d0dcc2a..274c6d5 100644
--- a/src/test/regress/expected/horology.out
+++ b/src/test/regress/expected/horology.out
@@ -2708,6 +2708,30 @@ SELECT to_timestamp('2019021', 'YYYYWWD');
  Sun Jan 06 00:00:00 2019 PST
 (1 row)
 
+SELECT to_timestamp('201901-Monday', 'YYYYWW-Day');
+         to_timestamp         
+------------------------------
+ Mon Dec 31 00:00:00 2018 PST
+(1 row)
+
+SELECT to_timestamp('201901-Thursday', 'YYYYWW-Day');
+         to_timestamp         
+------------------------------
+ Thu Jan 03 00:00:00 2019 PST
+(1 row)
+
+SELECT to_timestamp('201901-SAT', 'YYYYWW-DY');
+         to_timestamp         
+------------------------------
+ Sat Jan 05 00:00:00 2019 PST
+(1 row)
+
+SELECT to_timestamp('201902-mon', 'YYYYWW-dy');
+         to_timestamp         
+------------------------------
+ Mon Jan 07 00:00:00 2019 PST
+(1 row)
+
 SELECT to_timestamp('2005300', 'YYYYDDD');
          to_timestamp         
 ------------------------------
diff --git a/src/test/regress/sql/horology.sql b/src/test/regress/sql/horology.sql
index 2ae64af..80a219c 100644
--- a/src/test/regress/sql/horology.sql
+++ b/src/test/regress/sql/horology.sql
@@ -376,6 +376,10 @@ SELECT to_timestamp('2019011', 'YYYYWWD');
 SELECT to_timestamp('2019013', 'YYYYWWD');
 SELECT to_timestamp('2019017', 'YYYYWWD');
 SELECT to_timestamp('2019021', 'YYYYWWD');
+SELECT to_timestamp('201901-Monday', 'YYYYWW-Day');
+SELECT to_timestamp('201901-Thursday', 'YYYYWW-Day');
+SELECT to_timestamp('201901-SAT', 'YYYYWW-DY');
+SELECT to_timestamp('201902-mon', 'YYYYWW-dy');
 
 SELECT to_timestamp('2005300', 'YYYYDDD');
 
-- 
2.20.1