Thread: [PATCH] Add a new pattern for zero-based months for Date/Time Formatting
Hi,
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.
Thanks,
Vincent Moreau
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.
Thanks,
Vincent Moreau
Attachment
Re: [PATCH] Add a new pattern for zero-based months for Date/Time Formatting
From
Peter Eisentraut
Date:
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: [PATCH] Add a new pattern for zero-based months for Date/Time Formatting
From
Christoph Berg
Date:
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