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-07-10 20:10:46
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

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

Since we're certainly not going to commit these patches as-presented,
and nothing has happened on this thread since early April, I've marked
both the CF entries as Returned With Feedback. If you do write a patch
to make to_date work as above, please file a new CF entry.

(BTW, having two CF entries pointing at the same email thread is
pretty confusing to our not-that-bright tools. It's probably
better to have just one entry per thread in the future.)

regards, tom lane

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Daniel Wood 2020-07-10 20:23:40 Wait profiling
Previous Message David Steele 2020-07-10 19:56:44 Re: Remove Deprecated Exclusive Backup Mode