[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