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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Mark Lorenz <postgres(at)four-two(dot)de>
Cc: Cleysson Lima <cleyssondba(at)gmail(dot)com>, pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: Re: Created feature for to_date() conversion using patterns 'YYYY-WW', 'YYYY-WW-D', 'YYYY-MM-W' and 'YYYY-MM-W-D'
Date: 2020-04-04 16:39:05
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Mark Lorenz <postgres(at)four-two(dot)de> writes:
> 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"

Yes, that's clearly what our code, and what Oracle's does too, given
the tests I showed upthread.

> 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?

The trick is to produce something sane. I think that a reasonable
precedent for this would be what to_date does with ISO-week fields:
you can ask it to parse IYYY-IW-ID but you can't mix that with regular
month/day/year fields. So for example, it seems like it'd be possible
to reconstruct a date from YYYY-WW-D, because that's enough to uniquely
identify a day. The D field isn't monotonically increasing within a
week, but nonetheless there's exactly one day in each YYYY-WW week that
has a particular D value. However you probably don't want to allow
inconsistent mixtures like YYYY-WW-ID, because that's just a mess (and
more than likely, it's a mistake). And I would not be in favor of
allowing YYYY-Q either, because that would not be enough to uniquely
identify a day, so there's really no point in allowing Q to enter into
to_date's considerations at all.

Whether there is actually any field demand for such a feature is
not clear to me. AFAICT Oracle doesn't support it.

regards, tom lane

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Corey Huinker 2020-04-04 16:52:29 Re: Add A Glossary
Previous Message Tom Lane 2020-04-04 15:22:32 Re: adding partitioned tables to publications