[PATCH] Add a new pattern for zero-based months for Date/Time Formatting
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
You can run a PostgreSQL built from this patch straight from Docker, with no checkout and no build:
docker run --rm -p 5432:5432 ghcr.io/hackorum-dev/postgres-patch:t51285psql -h localhost -U postgresBuilt from patchset v1 (message #1), July 27, 2026 at 09:46 AM.
Every patchset is also pushed to a branch of our PostgreSQL fork, so you can check out the same tree CI built. Without a PostgreSQL checkout:
git clone --branch t51285_1 https://github.com/hackorum-dev/postgres.gitIn a checkout you already have, add the fork once:
git remote add hackorum https://github.com/hackorum-dev/postgres.gitthen, for this patchset and every later one:
git fetch hackorum t51285_1 && git checkout t51285_1Patchset v1 (message #1) is on t51285_1
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
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