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, 'YYYY-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, 'YYYY-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, 'YYYY-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 'YYYY-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 'YYYY-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
