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
    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 – Erwin Brandstetter Oct 2 '19 at 23:34
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.

| improve this answer | |
  • 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 '19 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! – Laurenz Albe Oct 3 '19 at 8:52
  • I tried :) postgresql.org/message-id/… – S-Man Oct 9 '19 at 7:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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