2

I tried to convert a date string (year, week, day of week = 'YYYY-WW-D') into a date using the to_date() function.

I got these results (demo:db<>fiddle):

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

It is not clear to me, why this results in the same date for every day of a week.

If I take the ISO format instead ('IYYY-IW-ID') this works as expected (demo:db<>fiddle):

date string |  to_date()
-------------------------
'2019-1-1'  |  2018-12-31
'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

Why does it work for the ISO pattern, not for the standard pattern? What am I missing here?

1
  • 1
    What's more, SELECT to_date(('2019-1-8'), 'IYYY-IW-ID') should not result in '2019-01-06' but in an exception. This formatting code is hugely complex and there are still corner case errors. Looks like you found one. Consider a bug report, maybe after presenting your case in pgsql-general Oct 2, 2019 at 23:34

1 Answer 1

1

This behavior is documented:

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.

3
  • Ok, thanks. But nevertheless it feels like a bug. An documented one, but a bug. Or is there any known reason for that? Do you know any?
    – S-Man
    Oct 3, 2019 at 8:39
  • Probably nobody thought this was a sufficiently interesting thing to spend development effort on. I don't think that there is a deep reason. If you feel motivated, have a go! Oct 3, 2019 at 8:52
  • I tried :) postgresql.org/message-id/…
    – S-Man
    Oct 9, 2019 at 7:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.