Re: Created feature for to_date() conversion using patterns 'YYYY-WW', 'YYYY-WW-D', 'YYYY-MM-W' and 'YYYY-MM-W-D'
Hi,
I apologize for the mistake.
For the mailing list correspondence I created this mail account. But I
forgot to change the sender name. So, the "postgres" name appeared as
sender name in the mailing list. I changed it.
Kind regards,
Mark/S-Man42
Hi,
some days ago I ran into a problem with the to_date() function. I
originally described it on StackExchange:
https://dba.stackexchange.com/questions/250111/unexpected-behaviour-for-to-date-with-week-number-and-week-day
The problem:
If you want to parse a date string with year, week and day of week,
you can do this using the ISO week pattern: 'IYYY-IW-ID'. This works
as expected:
date string | to_date()
+
'2019-1-1' | 2018-12-31 -> Monday of the first week of the year
(defined as the week that includes the 4th of January)
'2019-1-2' | 2019-01-01
'2019-1-3' | 2019-01-02
'2019-1-4' | 2019-01-03
'2019-1-5' | 2019-01-04
'2019-1-6' | 2019-01-05
'2019-1-7' | 2019-01-06
'2019-2-1' | 2019-01-07
'2019-2-2' | 2019-01-08
But if you are trying this with the non-ISO pattern '-WW-D', the
result was not expected:
date string | to_date()
-
'2019-1-1' | 2019-01-01
'2019-1-2' | 2019-01-01
'2019-1-3' | 2019-01-01
'2019-1-4' | 2019-01-01
'2019-1-5' | 2019-01-01
'2019-1-6' | 2019-01-01
'2019-1-7' | 2019-01-01
'2019-2-1' | 2019-01-08
'2019-2-2' | 2019-01-08
As you can see, the 'D' part of the pattern doesn't influence the
resulting date.
The answer of Laurenz Albe pointed to a part of the documentation, I
missed so far:
"In to_timestamp and to_date, weekday names or numbers (DAY, D, and
related field types) are accepted but are ignored for purposes of
computing the result. The same is true for quarter (Q) fields."
(https://www.postgresql.org/docs/12/functions-formatting.html)
So, I had a look at the relevant code part. I decided to try a patch
by myself. Now it works as I would expect it:
date string | to_date()
-
'2019-1-1' | 2018-12-30 -> Sunday (!) of the first week of the year
(the first week is at the first day of year)
'2019-1-2' | 2018-12-31
'2019-1-3' | 2019-01-01
'2019-1-4' | 2019-01-02
'2019-1-5' | 2019-01-03
'2019-1-6' | 2019-01-04
'2019-1-7' | 2019-01-05
'2019-2-1' | 2019-01-06
'2019-2-2' | 2019-01-07
Furthermore, if you left the 'D' part, the date would be always set to
the first day of the corresponding week (in that case it is Sunday, in
contrast to the ISO week, which starts mondays).
To be consistent, I added similar code for the week of month pattern
('W'). So, using the pattern '-MM-W-D' yields in:
date string | to_date()
---
'2018-12-5-1' | 2018-12-23
'2018-12-6-1' | 2018-12-30
'2019-1-1-1' | 2018-12-30 -> First day (Su) of the first week of the
first month of the year
'2019-2-2-1' | 2019-02-03 -> First day (Su) of the second week of
February
'2019-10-3-5' | 2019-10-17 -> Fifth day (Th) of the third week of
October
If you left the 'D', it would be set to 1 as well.
The code can be seen here:
https://github.com/S-Man42/postgres/commit/534e6bd70e23864f385d60ecf187496c7f4387c9
I hope, keeping the code style of the surrounding code (especially the
ISO code) is ok for you.
Now the questions:
1. Although the ignorance of the 'D' pattern is well documented, does
the new behaviour might be interesting for you?
2. Does it work as you'd expect it?
3. Because this could be my very first contribution to the PostgreSQL
code base, I really want you to be as critical as possible. I am not
quite sure if I didn't miss something important.
4. Currently something like '2019-1-8' does not throw an exception but
results in the same as '2019-2-1' (8th is the same as the 1st of the
next week). On the other hand, currently, the ISO week conversion
gives out the result of '2019-1-7' for every 'D' >= 7. I am not sure
if this is better. I think a consistent exception handling should be
discussed separately (date roll over vs. out of range exception vs.
ISO week behaviour)
So far, I am very curious about your opinions!
Kind regards,
Mark/S-Man42
Re: Created feature for to_date() conversion using patterns 'YYYY-WW', 'YYYY-WW-D', 'YYYY-MM-W' and 'YYYY-MM-W-D'
Hi,
while preparing the patch for the Commitfest, I found a bug in the
to_char() function that is quite correlated with this issue:
SELECT to_char('1997-02-01'::date, '-WW-D')
returns: 1997-05-7 -> which is ok, I believe. Feb, 1st was on Saturday,
so counting from Sundays, it was day 7 of week 5.
SELECT to_char('1997-02-03'::date, '-WW-D')
returns: 1997-05-2 -> This cannot be. The input date is two days laters,
but the result is 5 days earlier. I'd expect 1997-06-2 as result, but
this occurs another week later:
SELECT to_char('1997-02-10'::date, '-WW-D')
This is wrong, because this should be week 7 instead. On the other hand,
the ISO week formats work very well.
I'll have a look at the code and try to fix it in the patch as well.
Kind regards,
Mark
Am 2019-10-08 17:49, schrieb Mark Lorenz:
Hi,
I apologize for the mistake.
For the mailing list correspondence I created this mail account. But I
forgot to change the sender name. So, the "postgres" name appeared as
sender name in the mailing list. I changed it.
Kind regards,
Mark/S-Man42
Hi,
some days ago I ran into a problem with the to_date() function. I
originally described it on StackExchange:
https://dba.stackexchange.com/questions/250111/unexpected-behaviour-for-to-date-with-week-number-and-week-day
The problem:
If you want to parse a date string with year, week and day of week,
you can do this using the ISO week pattern: 'IYYY-IW-ID'. This works
as expected:
date string | to_date()
+
'2019-1-1' | 2018-12-31 -> Monday of the first week of the year
(defined as the week that includes the 4th of January)
'2019-1-2' | 2019-01-01
'2019-1-3' | 2019-01-02
'2019-1-4' | 2019-01-03
'2019-1-5' | 2019-01-04
'2019-1-6' | 2019-01-05
'2019-1-7' | 2019-01-06
'2019-2-1' | 2019-01-07
'2019-2-2' | 2019-01-08
But if you are trying this with the non-ISO pattern '-WW-D', the
result was not expected:
date string | to_date()
-
'2019-1-1' | 2019-01-01
'2019-1-2' | 2019-01-01
'2019-1-3' | 2019-01-01
'2019-1-4' | 2019-01-01
'2019-1-5' | 2019-01-01
'2019-1-6' | 2019-01-01
'2019-1-7' | 2019-01-01
'2019-2-1' | 2019-01-08
'2019-2-2' | 2019-01-08
As you can see, the 'D' part of the pattern doesn't influence the
resulting date.
The answer of Laurenz Albe pointed to a part of the documentation, I
missed so far:
"In to_timestamp and to_date, weekday names or numbers (DAY, D, and
related field types) are accepted but are ignored for purposes of
computing the result. The same is true for quarter (Q) fields."
(https://www.postgresql.org/docs/12/functions-formatting.html)
So, I had a look at the relevant code part. I decided to try a patch
by myself. Now it works as I would expect it:
date string | to_date()
-
'2019-1-1' | 2018-12-30 -> Sunday (!) of the first week of the year
(the first week is at the first day of year)
'2019-1-2' | 2018-12-31
'2019-1-3' | 2019-01-01
'2019-1-4' | 2019-01-02
'2019-1-5' | 2019-01-03
'2019-1-6' | 2019-01-04
'2019-1-7' | 2019-01-05
'2019-2-1' | 2019-01-06
'2019-2-2' | 2019-01-07
Furthermore, if you left the 'D' part, the date would be always set to
the first day of the corresponding week (in that case it is Sunday, in
contrast to the ISO week, which starts mondays).
To be consistent, I added similar code for the week of month pattern
('W'). So, using the pattern '-MM-W-D' yields in:
date string | to_date()
---
'2018-12-5-1' | 2018-12-23
'2018-12-6-1' | 2018-12-30
'2019-1-1-1' | 2018-12-30 -> First day (Su) of the first week of the
first month of the year
'2019-2-2-1' | 2019-02-03 -> First day (Su) of the second week of
February
'2019-10-3-5' | 2019-10-17 -> Fifth day (Th) of the third week of
October
If you left the 'D', it would be set to 1 as well.
The code can be seen here:
https://github.com/S-Man42/postgres/commit/534e6bd70e23864f385d60ecf187496c7f4387c9
I hope, keeping the code style of the surrounding code (especially the
ISO code) is ok for you.
Now the questions:
1. Although the ignorance of the 'D' pattern is well documented, does
the new behaviour might be interesting for you?
2. Does it work as you'd expect it?
3. Because this could be my very first contribution to the PostgreSQL
code base, I really want you to be as critical as possible. I am not
quite sure if I didn't miss something important.
4. Currently something like '2019-1-8' does not throw an exception but
results in the same as '2019-2-1' (8th is the same as the 1st of the
next week). On the other hand, currently, the ISO week conversion
gives out the result of '2019-1-7' for every 'D' >= 7. I am not sure
if this is better. I think a consistent exception handling should be
discussed separately (date roll over vs. out of range exception vs.
ISO week behaviour)
So far, I am very curious about your opinions!
Kind regards,
Mark/S-Man42
Re: Created feature for to_date() conversion using patterns 'YYYY-WW', 'YYYY-WW-D', 'YYYY-MM-W' and 'YYYY-MM-W-D'
Hi,
I fixed the described issue in the to char() function.
The output of the current version is:
postgres=# SELECT to_char('1997-02-01'::date, '-WW-D');
to_char
-
1997-05-7
(1 row)
postgres=# SELECT to_char('1997-02-03'::date, '-WW-D');
to_char
-
1997-05-2
(1 row)
postgres=# SELECT to_char('1997-02-10'::date, '-WW-D');
to_char
-
1997-06-2
(1 row)
As you can see, the week day of the Feb 3rd - which is two days AFTER
Feb 1st - yields in a result which is 5 days BEFORE the earlier date,
which obviously cannot be. Furthermore, using the Gregorian calendar,
Feb 3rd is in week 6. So, the Feb 10th cannot be in week 6 as well.
The bug was, that the week day of Jan 1st was not considered in the
calculation of the week number. So, a possible offset has not been set.
New output:
postgres=# SELECT to_char('1997-02-03'::date, '-WW-D');
to_char
-
1997-06-2
(1 row)
postgres=# SELECT to_char('1997-02-01'::date, '-WW-D');
to_char
-
1997-05-7
(1 row)
postgres=# SELECT to_char('1997-02-10'::date, '-WW-D');
to_char
-
1997-07-2
(1 row)
---
Furthermore I adjusted the to_date() functionality for the WW-D pattern
as well. As said before in the thread, I know, ignoring the D part is
known and documented, but I think, if the ISO format recognizes the day
part, the non-ISO format should as well - especially when the "back"
operation does as well (meaning to_char()):
Output in the current version:
postgres=# SELECT to_date('2019-1-1', '-WW-D');
to_date
2019-01-01
(1 row)
postgres=# SELECT to_date('2019-1-2', '-WW-D');
to_date
2019-01-01
(1 row)
postgres=# SELECT to_date('2019-1-3', '-WW-D');
to_date
2019-01-01
(1 row)
postgres=# SELECT to_date('2019-1-7', '-WW-D');
to_date
2019-01-01
(1 row)
postgres=# SELECT to_date('2019-2-1', '-WW-D');
to_date
2019-01-08
(1 row)
New output:
postgres=# SELECT to_date('2019-1-1', '-WW-D');
to_date
2018-12-30
(1 row)
postgres=# SELECT to_date('2019-1-2', '-WW-D');
to_date
2018-12-31
(1 row)
postgres=# SELECT to_date('2019-1-3', '-WW-D');
to_date
2019-01-01
(1 row)
postgres=# SELECT to_date('2019-1-7', '-WW-D');
to_date
2019-01-05
(1 row)
postgres=# SELECT to_date('2019-2-1', '-WW-D');
to_date
2019-01-06
(1 row)
I added the patch as plain text attachment. It contains the code and, of
course, the regression tests. Some existing tests failed, because they
worked with the old output. I have changed their expected output.
Hope you'll find it helpful.
Best regards,
Mark LorenzFrom 39b759221a827c7730d940ac14e7a28a7a76 Mon Sep 17 00:00:00 2001
From: Mark Lorenz
Date: Fri, 20 Dec 2019 14:44:42 +0100
Subject: [PATCH] fix issues with date format -WW-D in to_date() and
to_char(); adjusted and added regression tests
---
src/backend/utils/adt/formatting.c| 46 +++-
src/backend/utils/adt/timestamp.c | 121 +++
src/include/utils/timestamp.h | 7 +
src/test/regress/expected/horology.out| 66 +-
src/test/regress/expected/timestamp.out | 238 -
src/test/regress/expected/timestamptz.out | 242 +-
src/test/regress/sql/horology.sql | 4 +
src/test/regress/sql/timestamp.sql| 2 +
src/test/regress/sql/timestamptz.sql | 2 +
9 files changed, 514 insertions(+), 214 deletions(-)
diff --git a/src/backend/utils/adt/formatting.c b/src/backend/utils/adt/formatting.c
index 8fcbc22..ea6e45d 100644
--- a/src/backend/utils/adt/formatting.c
+++ b/src/backend/utils/adt/formatting.c
@@ -3017,7 +3017,7 @@ DCH_to_char(FormatNode *node, bool is_interval, TmToChar *in, char *out, Oid col
break;
case DCH_WW:
sprintf(s, "%0*d", S_FM(n->suffix) ? 0 : 2,
- (tm->tm_yday - 1) / 7 + 1);
+ date2week(tm->tm_year, tm->tm_mon, tm->tm_mday));
if (S_THth(n->suffix))
str_numth(s, s, S_TH_TYPE(n->suffix));
s += strlen(s);
@@ -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
Re: Created feature for to_date() conversion using patterns 'YYYY-WW', 'YYYY-WW-D', 'YYYY-MM-W' and 'YYYY-MM-W-D'
Hi,
I got the advice to split the patches for:
- fixing the to_char() function
- changing the to_date()/to_timestamp() behaviour
So I appended the split patches.
Kind regards,
Mark Lorenz
From 4e35bd88bef1916e7d11ad0776b3075e3183f7d0 Mon Sep 17 00:00:00 2001
From: Mark Lorenz
Date: Fri, 20 Dec 2019 14:30:41 +0100
Subject: [PATCH] change to_date()/to_timestamp() behaviour with '-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.
di
Re: Created feature for to_date() conversion using patterns 'YYYY-WW', 'YYYY-WW-D', 'YYYY-MM-W' and 'YYYY-MM-W-D'
Hi Tom,
thanks for answering!
I commited two different patches:
---
The first one is for the strange behaviour of to_char(), which could be
seen as a bug, I believe. As described earlier, to_char() with the
'WW-D' pattern could return wrong week numbers.
The non-ISO week number is defined for weeks beginning with Sundays and
ending with Saturdays. The first week of the year is the week with
January, 1st.
For example:
postgres=# SELECT to_char('1997-01-01'::date, '-WW-D');
to_char
-
1997-01-4
(1 row)
1997-01-01 was a Wednesday. So the first week in 1997 was from Jan 1st
to Jan 4th (Saturday). Week 2 started on Jan 5th. But to_char() gives
out week number 1 until Tuesday (!), Jan 7th.
postgres=# SELECT to_char('1997-01-07'::date, '-WW-D');
to_char
-
1997-01-3
(1 row)
After that, on Jan 8th, the output switches from 01-3 to 02-4, which
makes no sense in my personal opinion. The week number should be
consistent from Sun to Sat and should not switch during any day in the
week. Furthermore, it is not clear why Jan 7th should return an earlier
week day (3) than Jan 1st (4).
The bug is, that the calculation of the week number only considers the
number of days of the current year. But it ignores the first week day,
which defines an offset. This has been fixed in the first patch.
---
Second patch:
As you stated correctly, this is not a bug fix, because the current
behaviour is documented and it works as the documentation states. I
tried to describe my confusion in the very first post of this thread:
I was wondering why the D part is not recognized in the non-ISO week
pattern while the ISO day is working very well. Although this is
documented, there could be a chance that this simply was not implemented
right now - so I tried.
The main aspect, I believe, is, that to_date() or to_timestamp() is some
kind of "back" operation of the to_char() function. So, a new definition
simply should recognize the week day as the to_char() function does,
instead of setting the day part fix to any number (please see the
examples in the very first post for that).
---
Combining both patches, the to_char() fix and the to_date() change, it
is possible to calculate the non-ISO week pattern in both directions:
SELECT to_date(to_char(anydate, '-WW-D'), '-WW-D')
would result in "anydate". Currently it does not:
postgres=# SELECT to_date(to_char('1997-01-07'::date, '-WW-D'),
'-WW-D')
to_char
-
1997-01-01
(1 row)
postgres=# SELECT to_char(to_date('1997-01-07', '-WW-D'),
'-WW-D')
to_char
-
1997-01-04
(1 row)
On the other hand, the ISO week calculations work as expected,
especially the there-and-back operation results in the original value:
postgres=# SELECT to_date(to_char('1997-01-07'::date, 'IYYY-IW-ID'),
'IYYY-IW-ID')
to_char
-
1997-01-07
(1 row)
postgres=# SELECT to_char(to_date('1997-01-07', 'IYYY-IW-ID'),
'IYYY-IW-ID')
to_char
-
1997-01-7
(1 row)
The only difference between ISO and non-ISO weeks is the beginning on
Mondays and the definition of the first week. But this cannot be the
reason why one operation results in right values (comparing with a
calendar) and the other one does not.
Does this explanation make it clearer?
Re: Created feature for to_date() conversion using patterns 'YYYY-WW', 'YYYY-WW-D', 'YYYY-MM-W' and 'YYYY-MM-W-D'
while preparing the patch for the Commitfest, I found a bug in the
to_char() function that is quite correlated with this issue:
SELECT to_char('1997-02-01'::date, '-WW-D')
returns: 1997-05-7 -> which is ok, I believe. Feb, 1st was on
Saturday,
so counting from Sundays, it was day 7 of week 5.
SELECT to_char('1997-02-03'::date, '-WW-D')
returns: 1997-05-2 -> This cannot be.
Why not? These format codes are specified as
D day of the week, Sunday (1) to Saturday (7)
WW week number of year (1–53) (the first week starts on the first day
of the year)
Because 1997-05-2 is earlier than 1997-05-7. But 1997-02-03 is later
than 1997-02-01. From my point of view, this is confusing.
I don't see anything there that says that "D" is correlated with "WW".
We do have a connection between "ID" and "IW", so that ID ought to
specify a day within an IW week, but there's no connection between "D"
and either "W" or "WW" week numbering. It's a day of the week, as
per the regular calendar. Trying to define it as something else is
just going to break stuff.
The only way to make "D" as it stands compatible with a week-numbering
system is to ensure that your weeks always start on Sundays, that is,
just as confusing as ISO weeks but slightly differently confusing.
Perhaps it would be worth inventing format codes that do have the
same relationship to "W" and/or "WW" as "ID" does to "IW". But
repurposing "D" for that is a bad idea.
regards, tom lane
I don't want to create any connection here. The day is calculated
correctly. But the week number is wrong. 1997-02-03 was in week number
6, as well as 1997-02-04. But Postgres returns 5. The problem with
to_char() is, that the week number is considering only the nmber of days
in the year and divides them by 7. So, there is no diffence whether the
year starts on Sunday or any other week day. So, an offset is missing,
which yields in wrong week numbers, as I can see...
Re: Created feature for to_date() conversion using patterns 'YYYY-WW', 'YYYY-WW-D', 'YYYY-MM-W' and 'YYYY-MM-W-D'
Updated the chg_to_date_wwd.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_wwd.patch from
f4e740a8de3ad1e762a28f6ff253ea4f%40four-two.de is still up-to-date)From 4e35bd88bef1916e7d11ad0776b3075e3183f7d0 Mon Sep 17 00:00:00 2001
From: Mark Lorenz
Date: Fri, 20 Dec 2019 14:30:41 +0100
Subject: [PATCH 1/3] change to_date()/to_timestamp() behaviour with
'-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,
Re: Created feature for to_date() conversion using patterns 'YYYY-WW', 'YYYY-WW-D', 'YYYY-MM-W' and 'YYYY-MM-W-D'
Hi Tom, with a bit space to this issue, I re-read your comments. I am beginning to understand what you mean or - better - what's wrong with my thoughts. When I understand you correctly, you say, the WW can start at any weekday, and is not fixed to Sunday, right? In your opinion the WW starts with the weekday of Jan, 1st? That's what could be my problem: I always thought (maybe triggered through the D pattern), that WW has to start sundays. But, now I agree with you, the docs fit better to your interpretation: "the first week starts on the first day of the year" I interpreted it with: It starts on the week, which includes the first of the year, but the Sunday before. Did I understand you correctly? In that case, I accept, that my patch is no bugfix (I think, it would be one, if my interpretion would be the expected behaviour.). But, nevertheless, what about adding the function to accept the DAY, D (and maybe the Q) patterns for to_date() - in this case, of course, in the uncorrelated version? to_char() handles them properly. And, from my point of view, there is no reason why they should give "1" instead the real day number. What do you think?
