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

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

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

postgres-8
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


Reply | Threaded
Open this post in threaded view
|

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

postgres-8
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