to_date()/to_timestamp() silently accept month=0 and day=0
Hi,
I found what looks like a bug in to_date() / to_timestamp(). (Saw it in
master branch)
Inputs with month = 00 or day = 00 are accepted silently and normalized to
January / day 1, instead of being rejected as out of range.
Simple repro steps:
SELECT to_date('2024-00-15', 'YYYY-MM-DD');
SELECT to_date('2024-01-00', 'YYYY-MM-DD');
SELECT to_timestamp('2024-00-15', 'YYYY-MM-DD');
SELECT to_timestamp('2024-01-00', 'YYYY-MM-DD');
Observed results here:
to_date('2024-00-15', 'YYYY-MM-DD') -> 2024-01-15
to_date('2024-01-00', 'YYYY-MM-DD') -> 2024-01-01
to_timestamp('2024-00-15', 'YYYY-MM-DD') -> 2024-01-15 00:00:00+05:30
to_timestamp('2024-01-00', 'YYYY-MM-DD') -> 2024-01-01 00:00:00+05:30
I would expect all four calls to error, similar to how an invalid date
literal
is rejected.
Looking at the root cause in src/backend/utils/adt/formatting.c, the issue
seems
rooted in the interaction between ZERO_tm() and do_to_timestamp().
When to_date()/to_timestamp() initializes pg_tm, it uses ZERO_tm() which
defaults tm->tm_mday = 1 and tm->tm_mon = 1. The TmFromChar struct (tmfc),
which is used to collect the parsed inputs, stores these fields as plain
integers without independent presence flags (has_mm or has_dd have
booleans).
In do_to_timestamp(), the parsed values are applied back into the pg_tm
struct
using simple truthiness checks:
if (tmfc.mm) tm->tm_mon = tmfc.mm;
if (tmfc.dd) tm->tm_mday = tmfc.dd;
Because an explicitly parsed 00 evaluates to false, the zeros are never
copied
into pg_tm. Thus, the 1 defaults from ZERO_tm() remain untouched, and the
subsequent validation function ValidateDate() never actually sees the 0 to
throw
an out-of-bounds error.
Fixing this likely requires adding boolean flags to TmFromChar to
distinguish
between an omitted field and an explicitly parsed 0?
Regards,
Ayush
On Wed, Apr 22, 2026 at 07:48:00PM +0530, Ayush Tiwari wrote:
Inputs with month = 00 or day = 00 are accepted silently and normalized to
January / day 1, instead of being rejected as out of range.Simple repro steps:
SELECT to_date('2024-00-15', 'YYYY-MM-DD');
SELECT to_date('2024-01-00', 'YYYY-MM-DD');
SELECT to_timestamp('2024-00-15', 'YYYY-MM-DD');
SELECT to_timestamp('2024-01-00', 'YYYY-MM-DD');Observed results here:
to_date('2024-00-15', 'YYYY-MM-DD') -> 2024-01-15
to_date('2024-01-00', 'YYYY-MM-DD') -> 2024-01-01
to_timestamp('2024-00-15', 'YYYY-MM-DD') -> 2024-01-15 00:00:00+05:30
to_timestamp('2024-01-00', 'YYYY-MM-DD') -> 2024-01-01 00:00:00+05:30I would expect all four calls to error, similar to how an invalid date
literal is rejected.
While I agree with your feeling that it would be less confusing if
these patterns are rejected, throwing an error could also mean an
impact on existing applications that relied on the existing historical
behavior of replacing these zeroes defined in input, where they'd
expect a 01. So that would be a silent behavior change introduced in
a minor release.
Perhaps we could consider strengthening such inputs on HEAD once v20
opens for business? It would be really a scary thing to backpatch,
still a major release is a different thing.
Any thoughts or opinions from others?
--
Michael
On 23 Apr 2026, at 09:57, Michael Paquier <michael@paquier.xyz> wrote:
Perhaps we could consider strengthening such inputs on HEAD once v20
opens for business? It would be really a scary thing to backpatch,
still a major release is a different thing.
This could definitely not be backpatched IMO, a quick check in v14 shows the
same behaviour. The gregorian calendar goes from BC1 to AD1 and does not
define a year 0, to_date('0000','YYYY') correctly returns year 0001, handling
months/days in the same way at least makes it consistent (though I didn't scour
the archives to see if it was intentionally done like that).
--
Daniel Gustafsson
Hi,
On Thu, 23 Apr 2026 at 13:41, Daniel Gustafsson <daniel@yesql.se> wrote:
On 23 Apr 2026, at 09:57, Michael Paquier <michael@paquier.xyz> wrote:
Perhaps we could consider strengthening such inputs on HEAD once v20
opens for business? It would be really a scary thing to backpatch,
still a major release is a different thing.This could definitely not be backpatched IMO, a quick check in v14 shows
the
same behaviour. The gregorian calendar goes from BC1 to AD1 and does not
define a year 0, to_date('0000','YYYY') correctly returns year 0001,
handling
months/days in the same way at least makes it consistent (though I didn't
scour
the archives to see if it was intentionally done like that).
++ on not backporting it since it may break existing applications.
But we should consider strengthening such inputs for v20.
Regards,
Ayush
Daniel Gustafsson <daniel@yesql.se> writes:
On 23 Apr 2026, at 09:57, Michael Paquier <michael@paquier.xyz> wrote:
Perhaps we could consider strengthening such inputs on HEAD once v20
opens for business? It would be really a scary thing to backpatch,
still a major release is a different thing.
This could definitely not be backpatched IMO, a quick check in v14 shows the
same behaviour. The gregorian calendar goes from BC1 to AD1 and does not
define a year 0, to_date('0000','YYYY') correctly returns year 0001, handling
months/days in the same way at least makes it consistent (though I didn't scour
the archives to see if it was intentionally done like that).
Looking at the code, I think it intentionally interprets zero as
"missing data". See for example the stanza at formatting.c:4650ff
where tm_mon and tm_mday can be backfilled from a DDD field.
I'm disinclined to change the behavior around this; you're far
more likely to get complaints than kudos.
regards, tom lane
Hi,
On Thu, 23 Apr 2026 at 21:10, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Looking at the code, I think it intentionally interprets zero as
"missing data". See for example the stanza at formatting.c:4650ff
where tm_mon and tm_mday can be backfilled from a DDD field.I'm disinclined to change the behavior around this; you're far
more likely to get complaints than kudos.
If we are not going to change the behaviour, imo, we should
atleast add a test case to depict this scenario and comment
that says this is expected behaviour according to the community.
Regards,
Ayush
On Thu, Apr 23, 2026 at 10:06:41PM +0530, Ayush Tiwari wrote:
If we are not going to change the behaviour, imo, we should
atleast add a test case to depict this scenario and comment
that says this is expected behaviour according to the community.
Yes, we could close the gap with some tests, at least.
--
Michael
On 24 Apr 2026, at 00:14, Michael Paquier <michael@paquier.xyz> wrote:
On Thu, Apr 23, 2026 at 10:06:41PM +0530, Ayush Tiwari wrote:
If we are not going to change the behaviour, imo, we should
atleast add a test case to depict this scenario and comment
that says this is expected behaviour according to the community.Yes, we could close the gap with some tests, at least.
+1. Do you want to work on this Ayush?
--
Daniel Gustafsson
Hi,
On Fri, 24 Apr 2026 at 13:42, Daniel Gustafsson <daniel@yesql.se> wrote:
On 24 Apr 2026, at 00:14, Michael Paquier <michael@paquier.xyz> wrote:
On Thu, Apr 23, 2026 at 10:06:41PM +0530, Ayush Tiwari wrote:
If we are not going to change the behaviour, imo, we should
atleast add a test case to depict this scenario and comment
that says this is expected behaviour according to the community.Yes, we could close the gap with some tests, at least.
+1. Do you want to work on this Ayush?
Yes, I'll send a patch with test case today.
Regards,
Ayush
Hi,
On Fri, 24 Apr 2026 at 13:46, Ayush Tiwari <ayushtiwari.slg01@gmail.com>
wrote:
Hi,
On Fri, 24 Apr 2026 at 13:42, Daniel Gustafsson <daniel@yesql.se> wrote:
On 24 Apr 2026, at 00:14, Michael Paquier <michael@paquier.xyz> wrote:
On Thu, Apr 23, 2026 at 10:06:41PM +0530, Ayush Tiwari wrote:
If we are not going to change the behaviour, imo, we should
atleast add a test case to depict this scenario and comment
that says this is expected behaviour according to the community.Yes, we could close the gap with some tests, at least.
+1. Do you want to work on this Ayush?
Yes, I'll send a patch with test case today.
Attached patch, please review.
Regards,
Ayush
Attachments:
0001-Add-regression-tests-for-to_date-to_timestamp-zero-m.patchapplication/octet-stream; name=0001-Add-regression-tests-for-to_date-to_timestamp-zero-m.patchDownload+42-1
On 24 Apr 2026, at 11:13, Ayush Tiwari <ayushtiwari.slg01@gmail.com> wrote:
Attached patch, please review.
LGTM for the most part, I don't really think we need to use both to_date and
to_timestamp though, we can save a few cycles there. I rewrote the comments to
match the rest of the file, and moved to where to where we test for year 0000
since it seems like a better place. Also took the liberty to use year 100 in
one of the testcase, while the year is superfluous for the test in question,
year 100 was previously untested so this will increase test coverage for free.
--
Daniel Gustafsson
Attachments:
v2-0001-Add-regression-tests-for-zero-month-day-handling-.patchapplication/octet-stream; name=v2-0001-Add-regression-tests-for-zero-month-day-handling-.patch; x-unix-mode=0644Download+30-1
On Fri, 24 Apr 2026 at 18:14, Daniel Gustafsson <daniel@yesql.se> wrote:
On 24 Apr 2026, at 11:13, Ayush Tiwari <ayushtiwari.slg01@gmail.com>
wrote:
Attached patch, please review.
LGTM for the most part, I don't really think we need to use both to_date
and
to_timestamp though, we can save a few cycles there. I rewrote the
comments to
match the rest of the file, and moved to where to where we test for year
0000
since it seems like a better place. Also took the liberty to use year 100
in
one of the testcase, while the year is superfluous for the test in
question,
year 100 was previously untested so this will increase test coverage for
free.
Looks good to me. Thank you!
Regards,
Ayush
On 23.04.26 17:40, Tom Lane wrote:
Daniel Gustafsson <daniel@yesql.se> writes:
On 23 Apr 2026, at 09:57, Michael Paquier <michael@paquier.xyz> wrote:
Perhaps we could consider strengthening such inputs on HEAD once v20
opens for business? It would be really a scary thing to backpatch,
still a major release is a different thing.This could definitely not be backpatched IMO, a quick check in v14 shows the
same behaviour. The gregorian calendar goes from BC1 to AD1 and does not
define a year 0, to_date('0000','YYYY') correctly returns year 0001, handling
months/days in the same way at least makes it consistent (though I didn't scour
the archives to see if it was intentionally done like that).Looking at the code, I think it intentionally interprets zero as
"missing data". See for example the stanza at formatting.c:4650ff
where tm_mon and tm_mday can be backfilled from a DDD field.I'm disinclined to change the behavior around this; you're far
more likely to get complaints than kudos.
Complaints from whom? Oracle rejects these, and PostgreSQL generally
also rejects these dates/times in other contexts. I think this should
be rejected.
On Fri, Apr 24, 2026 at 05:41:50PM +0200, Peter Eisentraut wrote:
Complaints from whom? Oracle rejects these, and PostgreSQL generally also
rejects these dates/times in other contexts. I think this should be
rejected.
From folks who hypothetically rely on the existing zeroed inputs. :)
Perhaps it's a far-fetched argument, and just to be clear I would not
really object to a change of behavior on HEAD for v20, just to see
where it goes in 2027.
--
Michael
On Fri, Apr 24, 2026 at 02:44:04PM +0200, Daniel Gustafsson wrote:
LGTM for the most part, I don't really think we need to use both to_date and
to_timestamp though, we can save a few cycles there. I rewrote the comments to
match the rest of the file, and moved to where to where we test for year 0000
since it seems like a better place. Also took the liberty to use year 100 in
one of the testcase, while the year is superfluous for the test in question,
year 100 was previously untested so this will increase test coverage for free.
That seems fine to me. If we decide to change this behavior later on
and error on these pattern, at least we'll know about them.
--
Michael
Hi,
On Mon, 27 Apr 2026 at 12:53, Michael Paquier <michael@paquier.xyz> wrote:
On Fri, Apr 24, 2026 at 02:44:04PM +0200, Daniel Gustafsson wrote:
LGTM for the most part, I don't really think we need to use both to_date
and
to_timestamp though, we can save a few cycles there. I rewrote the
comments to
match the rest of the file, and moved to where to where we test for year
0000
since it seems like a better place. Also took the liberty to use year
100 in
one of the testcase, while the year is superfluous for the test in
question,
year 100 was previously untested so this will increase test coverage for
free.
That seems fine to me. If we decide to change this behavior later on
and error on these pattern, at least we'll know about them.
+1.
(Just one tiny nit for whenever this gets committed: in the v2 inline
comment,
"0 -> 1'st" might be slightly cleaner as "0 -> 1st" or "0 -> 1").
Regards,
Ayush
Hi,
On Mon, 27 Apr 2026 at 04:13, Michael Paquier <michael@paquier.xyz> wrote:
On Fri, Apr 24, 2026 at 05:41:50PM +0200, Peter Eisentraut wrote:
Complaints from whom? Oracle rejects these, and PostgreSQL generally
also
rejects these dates/times in other contexts. I think this should be
rejected.From folks who hypothetically rely on the existing zeroed inputs. :)
Perhaps it's a far-fetched argument, and just to be clear I would not
really object to a change of behavior on HEAD for v20, just to see
where it goes in 2027.
I'll add this to the commitfest, so that we can circle back on this
once v20 development starts.
I think parallely we can commit Daniel's v2 patch, since
that correctly documents what Postgres has right now, and we'll
know what exactly to change if we decide to go the other way.
Regards,
Ayush
On Thu, Apr 30, 2026 at 07:44:24PM +0530, Ayush Tiwari wrote:
I'll add this to the commitfest, so that we can circle back on this
once v20 development starts.I think parallely we can commit Daniel's v2 patch, since
that correctly documents what Postgres has right now, and we'll
know what exactly to change if we decide to go the other way.
I'll leave that up to Daniel, I guess, as he has sent the patch.
All this stuff qualifies as v20 to me, including the new tests. Now,
I don't see a reason against committing the new tests now. I doubt
that this are going to impact the buildfarm, and we are still early in
the beta period. I may be wrong about the buildfarm part, of course.
:)
--
Michael