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