Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nEzsv-00053z-Qi for pgsql-bugs@arkaria.postgresql.org; Tue, 01 Feb 2022 20:36:18 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1nEzsu-0007hd-M8 for pgsql-bugs@arkaria.postgresql.org; Tue, 01 Feb 2022 20:36:16 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nEzsu-0007gN-0O for pgsql-bugs@lists.postgresql.org; Tue, 01 Feb 2022 20:36:16 +0000 Received: from sonic311-14.consmr.mail.bf2.yahoo.com ([74.6.131.124]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1nEzsq-00035m-UV for pgsql-bugs@lists.postgresql.org; Tue, 01 Feb 2022 20:36:14 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1643747770; bh=MGCftyLeMgMQJeaZCFY42URLNpchhMamm0BK7T4+etk=; h=Date:From:To:Cc:In-Reply-To:References:Subject:From:Subject:Reply-To; b=aJTmbZDNuJUUVO5rSQSqqgrLp2d5Wf+hg5FkwZ6+80cc4P3RR14m9gn3gQ89PfF5qYEK01QARHrKfy/tuB5CEjOqz0H8+axub0AOMsZgi54rRBrdEMijqqkeOm5PskJcoZvPMmgEHcliNLeMkkbGjK29S7p37Nnp4wcmG3xWTLRB+MDsLhE4nYMtm8Lv6l6aDZOEtXRXkM+bLH2fciKfFkTNxN4da8XuUbDJ2QINKI1fSg4q644wikJXMwSm5AL4Rd8BIor2ApjvZ2oDqA+DIUEI+q9M4Ns3h/TB5eGu6gjMaGP8yFsya2UD4o99cU+7Z/crdlJipqqXndi8hs1lGQ== X-SONIC-DKIM-SIGN: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1643747770; bh=g2o278gZ5/BNc5vl7ksh5jjC6AYfNGb5n3Nb8Q0ixxy=; h=X-Sonic-MF:Date:From:To:Subject:From:Subject; b=PxJKVlTvu6OPCiI3LqZN81//YSX4utDnifRDJDVw7A0l8Jw/ozh89GqwN2YkEKSN1SJyll32XjLJ8AfBHhnMxxe7Juj98+juCBNCNsls51nGxkxsJ1XltHiTYQ+6QvcIU+CF/GZO1nTzzKkrtbdBGtwCCvGBml3lWgAbCFNYNncQJ88S7cDVmIMshWEdjZ3QEmpMckDTJ0pIr7nbtXdnlwtA0bBl0nX3rwPqAk4etULRclHlVLDic8iGxTLZNk6CPCZ1Ct6YdrKnKBz5VwpRWNbApS9Ur4PnhxkjiBrarbc/Q29yqivjn8sWKfOSdo9YjoY/XonEUF6Qc+RfWCe6uA== X-YMail-OSG: alvRWK8VM1kd3HnQL8RhgtbuXeqIJV7B2zG5jL2j9.21TYovKoS6dWGly8XmK1F fHy1NUvKNa69BXc_vqs4XdgdmSou_gc_3Bq0b56uFyV8C1F3.0e1cvQjUJqTFQ9m5zn9P8TOnBoy Hv00xGPBsA1LNAkJPpGrNofARyviUBLa.ujreUeVdpHGca2wPGwqdmdeV6NSdpY3gqJl.TJgoEn_ uG_keQNx2ayEKkRFbazxFG5168dn_kfuCiWZogGOOnmj4qxCyJgJedXxlQPOgrPwXmz2UBmA.uT_ GZ361n_oFyA8BLvJHYhOjzyd9TBGWETaxQGXDT9_zxMptLK4zCfItUTKROdvr6F0mx8qmm77danp UrLxuSzUcagOlI3_q.a0lKYt0kPP_.aLFI.zXhJhsFNZxNbJA4w6WafUAM44aYu9HfqDq0DEvjBU i1IOo5sh3m1qT3BGUJTq0BNT.HyLXT9F8SWU3qLtWM9nivuZP7NwcZ085zGNkosrqTynpyg8ZGyD gHgXcJwoUwFQLv1RV7FXZkslMYfoyr6xQB.iG_yqmOfrm9VqgSsIwDSy0CvmEJemUKpjN5wTeeYl jruhjx5ujZqkICsiRhUuyZklhaQ3r4eHvraMKYoieyLkXYYKCXMD7RtPGArE.Jpwrv3hJQeXBNPj l9gDQo6oGvPJ6hs2ZIa.OqfEUxRjz0GLel3G5DP3.vK5QDj882NJnW0uwWujMSKfMl_eLFSie8Ln YZssROZhcYbLzRmV4ICWrRdxRfmWUIDtqAJ0jKQW8i.yIi_GwXowmep7tLHNubuvmy_M3hg4RJ4x wzAX2Lnjyr68WnpkpAKOKuPOe2Sq8G07vmJMYKscbHyb.Ny6_ff0RkTQHmheOxaV2TJLuvpyqlb0 .QMuvQcbJn7NYro.nZuKzfLoqDst8VJIbGSjJp34.h733n_ac7k6jSisMB5QsKNvRLDC4Z2haFWK htxOpMBRMgAAgLCZFACihe3VsP2aiJhYV5dFHuoxnmHt2xHtHNsutAXwPzw3Fo6Xm9ImUhWMv7T0 xaLBUV67qIwCJJl0Nq15LOobFljU5vBDMPKUAcDJ6oCeOkDC2zinyoqePzyyHy4tQRggZvqBH2aQ VvFxCYFt6xPWpfxEQIc8VdeJEuD0CP7GrAsyM.E5Qyh69mhZ0eUoGu2dCFeUuLOmqb2Jy9jxoVGw jnzDwEHpGymutyXC1H8i5kmx6Nd0gwYZGDRgbb1MG112C2Nmyh6UzsR.U2T3p.auNgKbPiQyRwi3 1TdxkT2npqoZTy9IchU4Of2yHDxyGW1T9Vlph9zfWtW6dhn7ZFuwXpGdy2keXcrm33cdAeqc0wy9 _DX3BviQH0S6E22hPoLekvbcPL1ITXX1X_7AqO858tqEi6gH1fQjVldybUIndIkKVHzMEpZhKxcw 5c_k3qiTw3.vhZEA9xCcxJrvFbDKNPrHOFh5zvAn6Da3S_yDgySQ_BnerdEZ6Jb.96LtnCFXxIcI 0IelbQGrC.UevoFd8O.BmJJTAKnOgXumwI0CmOK0bo7NTD.Y8th_6Qe3_rGoryz1d5i0tWaXh36s gfsT0VZAUFkYQDQ0k2gDLoA7L015JYHET3lSck9bjiFj.ugIoLTByFxpUbDRVm_CWKMlTqzrf_qM HIjrTF4Xp_VU8yDM1YwYQZArb7KQ8RCs_AN1VTp3FibfDaTxaylFS6MIcBOCguQMlrkmQI4h2XXf HY3YWGbJBr5IeTj33WkNyY0fG_lqWiKEh.AcrdDFXn262dBEo39KWgnLPrOIKD5ah8B3T0UkLt2Q DwDRqQ3fkLBa49ncjxRNpPtdmx116_1DGkrKMDw4Jm0okffEpHXj6832.iW61o6uvoTAZJLRDFuh ZqhGz4YDCYA6aYJznB3WT0T406rXMV2StD9kTqlFb6GRGd2d.5iHXtGmYoleI4_WBGgFA4w7gy3k 4iGc5QKXlwUQiDFiDbiBldJH5y0baoiFVnEYDIpfmxh1XocnGyQ7FN2l5Xy41Zc1zZsYlwAsY3jv CRDZXfo_NNPrQeVo6qnAF_QLuVtegwsk.l_T5k4Mq_5EzoKlIRoR0SAZH.9mkACfvVlym69d_fKc fWUY18.gNhpbGjAIuFKlKRIK9ZDH1ZLumQPalNgSAqsjXGqij_koVoe70mxr.4aR_5ZCVHePpU84 mQAJLAb4eFlX1HKqpBdGAGegIU_CTZXOhWNNqes1S3_f.4JGwZYSubbyqOUhbDA0qjkrK X-Sonic-MF: Received: from sonic.gate.mail.ne1.yahoo.com by sonic311.consmr.mail.bf2.yahoo.com with HTTP; Tue, 1 Feb 2022 20:36:10 +0000 Date: Tue, 1 Feb 2022 20:35:36 +0000 (UTC) From: "smk_va@yahoo.com" To: "David G. Johnston" , Tom Lane Cc: "pgsql-bugs@lists.postgresql.org" Message-ID: <248542100.2597403.1643747736697@mail.yahoo.com> In-Reply-To: <931069.1643730432@sss.pgh.pa.us> References: <17390-3aea53f5c03c0e4a@postgresql.org> <931069.1643730432@sss.pgh.pa.us> Subject: Re: BUG #17390: Function, to_date() -- unexpected values and a request MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_2597402_1538442704.1643747736694" X-Mailer: WebService/1.1.19615 YMailNorrin Content-Length: 12574 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------=_Part_2597402_1538442704.1643747736694 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable David and Tom, thanks for your thoughtful responses. I accept that with re= source constraints, backward compatibility/status quo reigns. Hopefully, th= is discussion will trigger someone's interest, and the team will give the g= o ahead for moving toward a more consistent implementation of to_date() and= its ilk. Accordingly, I propose some guideposts. As a basic premise, it seems fair to say that if the documentation specifie= s a range for a given field, that is a contract that the stated range is en= forced in the implementation. I recognize that refining the documentation m= ay be easier than modifying the implementation, so perhaps (<=3D 12) rather= than (01--12) as the range for 'MM', in Table 9.26 of=C2=A0https://www.pos= tgresql.org/docs/current/functions-formatting.html. Searching the web for "Oracle to_date()" yields a link to the reference doc= umentation from Oracle, which then includes a link to a detailed reference = on "Datetime Format Models". I'll skip the link, as the link may change bef= ore this gets a look ;-) (It appears that JSON datetime conversions follow = the ISO 8601 standards by convention. See, e.g.,=C2=A0https://www.w3docs.co= m/snippets/javascript/the-right-json-date-format.html. So no direct impact = of "jsonpath date conversions" on this discussion other than that the speci= fied ranges be enforced on corresponding fields, near as I can tell.) There seems to be a mismatch between the PostgreSQL implementation here, wh= ich ignores specified values for certain fields, versus the Oracle implemen= tation, which specifies that certain fields at the end of input strings can= be omitted. If the 'Q' field is omitted, and the implementation stores the= date corresponding to the first quarter, that is one thing; but if the 'Q'= field is specified as '3' and the implementation supplies '1', because it = is "ignoring" the 'Q' field, that is an altogether different matter, and th= e fact that the latter produces inconsistencies argues against the "ignorin= g" approach. The Oracle documentation does detail which formatting fields c= an be specified in the "to_* datetime functions", and which cannot, which i= s an approach David suggests, and which works well, provided that the imple= mentation flags the invalid format specification as an error rather than st= oring an inconsistent/unexpected value. I understand that changes in implementation have to go through a deprecatio= n cycle, and appreciate that someone will have to step up to get it done (I= 'm afraid my hacking is limited to high-level languages). PostgreSQL has a = well-deserved reputation for standards compliance and code quality, and it = would be great if the formatting functions could be brought up to snuff. Thanks,=C2=A0=C2=A0 =C2=A0 Murthy On Tuesday, February 1, 2022, 10:47:19 AM EST, Tom Lane wrote: =20 =20 "David G. Johnston" writes: > Pretend to_date doesn=E2=80=99t exist and just write a function that chec= ks for > valid inputs via RegEx and then parse it.=C2=A0 Maybe some day someone wi= ll > develop a new conversion function that has considerably stricter, and > self-defined, behavior (but given that to_date is basically =E2=80=9Cclos= e enough > to get the job done=E2=80=9D I=E2=80=99m not optimistic).=C2=A0 Until the= n, protect yourself. There are various things going on here: * To the extent that to_date() has an accepted charter, it's "work like the Oracle function of the same name".=C2=A0 Thus, arguments like "it'd be better for my use-case if it did X" are basically not going to find any traction.=C2=A0 Arguments like "Oracle's to_date() does X, so we should too" have a better chance.=C2=A0 Unfortunately, most of us lack access to an Oracle instance, making it hard to investigate such details. * The formatting.c code has recently been hacked up to also support jsonpath datetime conversions, meaning that there's a second source of truth involved; any proposed change would need to also be measured against whether it moves us closer to or further away from the relevant jsonpath standard.=C2=A0 That's another hard-to-get-hold-of reference, and I'm not sure how detailed its answers would be anyway. * There's also a fairly strong argument for maintaining backwards compatibility, even if the existing behavior is arguably wrong per the above points. * formatting.c is an ill-documented, unmaintainable mess that nobody is eager to touch.=C2=A0 (The original author is long gone.) These things combine to encourage leaving it at the status quo. Somebody who was really motivated and willing to get their hands dirty could perhaps get something done, but I don't think any of the current crop of hackers are very interested. =C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 regards, tom lane =20 ------=_Part_2597402_1538442704.1643747736694 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
David and Tom, thanks for yo= ur thoughtful responses. I accept that with resource constraints, backward = compatibility/status quo reigns. Hopefully, this discussion will trigger so= meone's interest, and the team will give the go ahead for moving toward a m= ore consistent implementation of to_date() and its ilk. Accordingly, I prop= ose some guideposts.

=
As a basic premise, it seems fair to= say that if the documentation specifies a range for a given field, that is= a contract that the stated range is enforced in the implementation. I reco= gnize that refining the documentation may be easier than modifying the impl= ementation, so perhaps (<=3D 12) rather than (01--12) as the range for '= MM', in Table 9.26 of https://ww= w.postgresql.org/docs/current/functions-formatting.html.

Searching the web for "Oracle to_date()" yields a link to the referenc= e documentation from Oracle, which then includes a link to a detailed refer= ence on "Datetime Format Models". I'll skip the link, as the link may chang= e before this gets a look ;-) (It appears that JSON datetime conversions fo= llow the ISO 8601 standards by convention. See, e.g., https://www.w3docs.com/snippets/javascript/= the-right-json-date-format.html. So no direct impact of "jsonpath date conversions" on this discussion= other than that the specified ranges be enforced on corresponding fields, = near as I can tell.)

There seems to be a mi= smatch between the PostgreSQL implementation here, which ignores specified = values for certain fields, versus the Oracle implementation, which specifie= s that certain fields at the end of input strings can be omitted. If the 'Q= ' field is omitted, and the implementation stores the date corresponding to= the first quarter, that is one thing; but if the 'Q' field is specified as= '3' and the implementation supplies '1', because it is "ignoring" the 'Q' = field, that is an altogether different matter, and the fact that the latter= produces inconsistencies argues against the "ignoring" approach. The Oracl= e documentation does detail which formatting fields can be specified in the= "to_* datetime functions", and which cannot, which is an approach David su= ggests, and which works well, provided that the implementation flags the in= valid format specification as an error rather than storing an inconsistent/= unexpected value.

I understand that changes in implementa= tion have to go through a deprecation cycle, and appreciate that someone wi= ll have to step up to get it done (I'm afraid my hacking is limited to high= -level languages). PostgreSQL has a well-deserved reputation for standards = compliance and code quality, and it would be great if the formatting functi= ons could be brought up to snuff.

Thanks, 
=
    Murthy

<= /div> =20
=20
On Tuesday, February 1, 2022, 10:47:19 AM EST, Tom Lane= <tgl@sss.pgh.pa.us> wrote:


"David G. Johnston" <david.g.johnston@gmail.com> writes:

> Pretend to_date do= esn=E2=80=99t exist and just write a function that checks for
> valid inputs via RegEx and then parse it.  Maybe some day so= meone will
> develop a new conversion function that ha= s considerably stricter, and
> self-defined, behavior = (but given that to_date is basically =E2=80=9Cclose enough
> to get the job done=E2=80=9D I=E2=80=99m not optimistic).  Unti= l then, protect yourself.


There = are various things going on here:

* To= the extent that to_date() has an accepted charter, it's "work
like the Oracle function of the same name".  Thus, arguments lik= e
"it'd be better for my use-case if it did X" are basica= lly not going
to find any traction.  Arguments like = "Oracle's to_date() does X,
so we should too" have a bett= er chance.  Unfortunately, most of us
lack access to= an Oracle instance, making it hard to investigate
such d= etails.

* The formatting.c code has re= cently been hacked up to also support
jsonpath datetime c= onversions, meaning that there's a second source
of truth= involved; any proposed change would need to also be measured
against whether it moves us closer to or further away from the relevan= t
jsonpath standard.  That's another hard-to-get-hol= d-of reference, and
I'm not sure how detailed its answers= would be anyway.

* There's also a fai= rly strong argument for maintaining backwards
compatibili= ty, even if the existing behavior is arguably wrong per
t= he above points.

* formatting.c is an = ill-documented, unmaintainable mess that nobody
is eager = to touch.  (The original author is long gone.)

These things combine to encourage leaving it at the status qu= o.
Somebody who was really motivated and willing to get t= heir hands
dirty could perhaps get something done, but I = don't think any of
the current crop of hackers are very i= nterested.

     &n= bsp;      regards, tom lane

------=_Part_2597402_1538442704.1643747736694--