Обсуждение: [PATCH] Add a new pattern for zero-based months for Date/Time Formatting

On 24.03.25 11:45, Vincent Moreau wrote:
> I came across date information from an external data source where the
> month number is zero-based (January = 0, December = 11) and found that
> I couldn't process it directly using the TO_DATE function.
> This patch introduces a new pattern (MZ) for handling zero-based
> months in Date/Time Formatting.
> 
> ## Example
> 
> SELECT to_date('01012025', 'DDMZYYYY');
> to_date
> ------------
> 2025-02-01
> 
> ## Implementation notes
> 
> This is my first patch submission. I have tried to follow the
> guidelines from various documents, but please let me know if anything
> is missing or not aligned with expectations. My apologies in advance
> for any oversights.

Welcome.  The patch looks pretty solid as such.  But the date formatting 
functions are tied into the SQL standard and/or Oracle compatibility, so 
we shouldn't just make up our own placeholders without analyzing how 
they fit into the larger scheme in terms of compatibility.  Moreover, if 
there are zero-based months, why not zero-based days, or any of the 
other fields?  I suspect that this is a pretty marginal use, and you 
might be better of trying to work around it externally.

Re: Peter Eisentraut
> Moreover, if there are zero-based months, why not zero-based days,
> or any of the other fields?

I would suspect this is coming from C's struct tm where tm_mon is
0..11 and all other fields being "normal". Used by asctime(), gmtime()
and friends.

Christoph

Christoph Berg <myon@debian.org> writes:
> Re: Peter Eisentraut
>> Moreover, if there are zero-based months, why not zero-based days,
>> or any of the other fields?

> I would suspect this is coming from C's struct tm where tm_mon is
> 0..11 and all other fields being "normal". Used by asctime(), gmtime()
> and friends.

FWIW, I agree completely with Peter's objections, and here's one more:
the last thing the world needs is yet another way in which datetime
strings can be ambiguous.  I think the right answer to the OP's
problem is to push back on the data source.

            regards, tom lane