public inbox for [email protected]
help / color / mirror / Atom feedRe: to_date()/to_timestamp() silently accept month=0 and day=0
8+ messages / 5 participants
[nested] [flat]
* Re: to_date()/to_timestamp() silently accept month=0 and day=0
@ 2026-04-23 15:40 Tom Lane <[email protected]>
2026-04-23 16:36 ` Re: to_date()/to_timestamp() silently accept month=0 and day=0 Ayush Tiwari <[email protected]>
2026-04-24 15:41 ` Re: to_date()/to_timestamp() silently accept month=0 and day=0 Peter Eisentraut <[email protected]>
0 siblings, 2 replies; 8+ messages in thread
From: Tom Lane @ 2026-04-23 15:40 UTC (permalink / raw)
To: Daniel Gustafsson <[email protected]>; +Cc: Michael Paquier <[email protected]>; Ayush Tiwari <[email protected]>; [email protected]
Daniel Gustafsson <[email protected]> writes:
>> On 23 Apr 2026, at 09:57, Michael Paquier <[email protected]> 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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: to_date()/to_timestamp() silently accept month=0 and day=0
2026-04-23 15:40 Re: to_date()/to_timestamp() silently accept month=0 and day=0 Tom Lane <[email protected]>
@ 2026-04-23 16:36 ` Ayush Tiwari <[email protected]>
2026-04-23 22:14 ` Re: to_date()/to_timestamp() silently accept month=0 and day=0 Michael Paquier <[email protected]>
1 sibling, 1 reply; 8+ messages in thread
From: Ayush Tiwari @ 2026-04-23 16:36 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Daniel Gustafsson <[email protected]>; Michael Paquier <[email protected]>; [email protected]
Hi,
On Thu, 23 Apr 2026 at 21:10, Tom Lane <[email protected]> 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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: to_date()/to_timestamp() silently accept month=0 and day=0
2026-04-23 15:40 Re: to_date()/to_timestamp() silently accept month=0 and day=0 Tom Lane <[email protected]>
2026-04-23 16:36 ` Re: to_date()/to_timestamp() silently accept month=0 and day=0 Ayush Tiwari <[email protected]>
@ 2026-04-23 22:14 ` Michael Paquier <[email protected]>
2026-04-24 08:12 ` Re: to_date()/to_timestamp() silently accept month=0 and day=0 Daniel Gustafsson <[email protected]>
0 siblings, 1 reply; 8+ messages in thread
From: Michael Paquier @ 2026-04-23 22:14 UTC (permalink / raw)
To: Ayush Tiwari <[email protected]>; +Cc: Tom Lane <[email protected]>; Daniel Gustafsson <[email protected]>; [email protected]
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
Attachments:
[application/pgp-signature] signature.asc (833B, 2-signature.asc)
download
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: to_date()/to_timestamp() silently accept month=0 and day=0
2026-04-23 15:40 Re: to_date()/to_timestamp() silently accept month=0 and day=0 Tom Lane <[email protected]>
2026-04-23 16:36 ` Re: to_date()/to_timestamp() silently accept month=0 and day=0 Ayush Tiwari <[email protected]>
2026-04-23 22:14 ` Re: to_date()/to_timestamp() silently accept month=0 and day=0 Michael Paquier <[email protected]>
@ 2026-04-24 08:12 ` Daniel Gustafsson <[email protected]>
0 siblings, 0 replies; 8+ messages in thread
From: Daniel Gustafsson @ 2026-04-24 08:12 UTC (permalink / raw)
To: Michael Paquier <[email protected]>; +Cc: Ayush Tiwari <[email protected]>; Tom Lane <[email protected]>; [email protected]
> On 24 Apr 2026, at 00:14, Michael Paquier <[email protected]> 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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: to_date()/to_timestamp() silently accept month=0 and day=0
2026-04-23 15:40 Re: to_date()/to_timestamp() silently accept month=0 and day=0 Tom Lane <[email protected]>
@ 2026-04-24 15:41 ` Peter Eisentraut <[email protected]>
2026-04-26 22:43 ` Re: to_date()/to_timestamp() silently accept month=0 and day=0 Michael Paquier <[email protected]>
1 sibling, 1 reply; 8+ messages in thread
From: Peter Eisentraut @ 2026-04-24 15:41 UTC (permalink / raw)
To: Tom Lane <[email protected]>; Daniel Gustafsson <[email protected]>; +Cc: Michael Paquier <[email protected]>; Ayush Tiwari <[email protected]>; [email protected]
On 23.04.26 17:40, Tom Lane wrote:
> Daniel Gustafsson <[email protected]> writes:
>>> On 23 Apr 2026, at 09:57, Michael Paquier <[email protected]> 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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: to_date()/to_timestamp() silently accept month=0 and day=0
2026-04-23 15:40 Re: to_date()/to_timestamp() silently accept month=0 and day=0 Tom Lane <[email protected]>
2026-04-24 15:41 ` Re: to_date()/to_timestamp() silently accept month=0 and day=0 Peter Eisentraut <[email protected]>
@ 2026-04-26 22:43 ` Michael Paquier <[email protected]>
2026-04-30 14:14 ` Re: to_date()/to_timestamp() silently accept month=0 and day=0 Ayush Tiwari <[email protected]>
0 siblings, 1 reply; 8+ messages in thread
From: Michael Paquier @ 2026-04-26 22:43 UTC (permalink / raw)
To: Peter Eisentraut <[email protected]>; +Cc: Tom Lane <[email protected]>; Daniel Gustafsson <[email protected]>; Ayush Tiwari <[email protected]>; [email protected]
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
Attachments:
[application/pgp-signature] signature.asc (833B, 2-signature.asc)
download
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: to_date()/to_timestamp() silently accept month=0 and day=0
2026-04-23 15:40 Re: to_date()/to_timestamp() silently accept month=0 and day=0 Tom Lane <[email protected]>
2026-04-24 15:41 ` Re: to_date()/to_timestamp() silently accept month=0 and day=0 Peter Eisentraut <[email protected]>
2026-04-26 22:43 ` Re: to_date()/to_timestamp() silently accept month=0 and day=0 Michael Paquier <[email protected]>
@ 2026-04-30 14:14 ` Ayush Tiwari <[email protected]>
2026-04-30 22:57 ` Re: to_date()/to_timestamp() silently accept month=0 and day=0 Michael Paquier <[email protected]>
0 siblings, 1 reply; 8+ messages in thread
From: Ayush Tiwari @ 2026-04-30 14:14 UTC (permalink / raw)
To: Michael Paquier <[email protected]>; +Cc: Peter Eisentraut <[email protected]>; Tom Lane <[email protected]>; Daniel Gustafsson <[email protected]>; [email protected]
Hi,
On Mon, 27 Apr 2026 at 04:13, Michael Paquier <[email protected]> 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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: to_date()/to_timestamp() silently accept month=0 and day=0
2026-04-23 15:40 Re: to_date()/to_timestamp() silently accept month=0 and day=0 Tom Lane <[email protected]>
2026-04-24 15:41 ` Re: to_date()/to_timestamp() silently accept month=0 and day=0 Peter Eisentraut <[email protected]>
2026-04-26 22:43 ` Re: to_date()/to_timestamp() silently accept month=0 and day=0 Michael Paquier <[email protected]>
2026-04-30 14:14 ` Re: to_date()/to_timestamp() silently accept month=0 and day=0 Ayush Tiwari <[email protected]>
@ 2026-04-30 22:57 ` Michael Paquier <[email protected]>
0 siblings, 0 replies; 8+ messages in thread
From: Michael Paquier @ 2026-04-30 22:57 UTC (permalink / raw)
To: Ayush Tiwari <[email protected]>; +Cc: Peter Eisentraut <[email protected]>; Tom Lane <[email protected]>; Daniel Gustafsson <[email protected]>; [email protected]
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
Attachments:
[application/pgp-signature] signature.asc (833B, 2-signature.asc)
download
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2026-04-30 22:57 UTC | newest]
Thread overview: 8+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-04-23 15:40 Re: to_date()/to_timestamp() silently accept month=0 and day=0 Tom Lane <[email protected]>
2026-04-23 16:36 ` Ayush Tiwari <[email protected]>
2026-04-23 22:14 ` Michael Paquier <[email protected]>
2026-04-24 08:12 ` Daniel Gustafsson <[email protected]>
2026-04-24 15:41 ` Peter Eisentraut <[email protected]>
2026-04-26 22:43 ` Michael Paquier <[email protected]>
2026-04-30 14:14 ` Ayush Tiwari <[email protected]>
2026-04-30 22:57 ` Michael Paquier <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox