Updated the chg_to_date_yyyywwd.patch with additional tests (because it
works not only for 'D' pattern but also for all day patterns like 'Day'
or 'DY'). Added the necessary documentation change.
(The fix_to_char_yyyywwd.patch from
f4e740a8de3ad1e762a28f6ff253ea4f%40four-two.de is still up-to-date)From 4e35bd88bef1916e7d11ad0776b3075e3183f7d0 Mon Sep 17 00:00:00 2001
From: Mark Lorenz <mark.lor...@four-two.de>
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 <mark.lor...@four-two.de>
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 <mark.lor...@four-two.de>
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
