> On 21-Oct-2021, at 17:15, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > > On 10/21/21 15:45, Bryn Llewellyn wrote: >>> /Adrian Klaver wrote:/ >>> >>>> /Bryn wrote:/ >>>> > >> Thanks, too, to David Johnston for your reply. Yes, I see now that the >> "10.1. Overview" page that starts the "Type Conversion" chapter does have >> lots of inflexions of the verb "prefer". And close to one of these there's a >> link to "Table 52.63" on the "52.62. pg_type" page. But I failed to spot >> that. >> You said "implicit casting to text is bad". Yes, all implicit casting is, at >> best, potentially confusing for human code readers. I aim religiously to >> avoid this and always aim to use an explicit typecast instead. > > This was explicitly dealt with in the Postgres 8.3 release: > > https://www.google.com/url?q=https://www.postgresql.org/docs/8.3/release-8-3.html&source=gmail-imap&ust=1635466561000000&usg=AOvVaw1Cm9kd4XZPydsVQ0qGU2a- > > E.24.2.1. General > > Non-character data types are no longer automatically cast to TEXT (Peter, > Tom) > >> And this brings me to what started me on this path today. "\df to_char" >> shows that while it has overloads for both plain "timestamp" and >> "timestamptz" date-time inputs, it has no "date" overload. Here's a > > That is because: > > https://www.google.com/url?q=https://www.postgresql.org/docs/14/functions-formatting.html&source=gmail-imap&ust=1635466561000000&usg=AOvVaw1VLjGNdZOaBaaAolnnrXtx > > to_char ( timestamp, text ) → text > to_char ( timestamp with time zone, text ) → text > Converts time stamp to string according to the given format. > to_char(timestamp '2002-04-20 17:31:12.66', 'HH12:MI:SS') → 05:31:12 > > to_char() expects a timestamp and per my previous post the preferred cast for > a date to a timestamp is to timestamptz. > > >> contrived test: >> deallocate all; >> prepare s as >> with c as ( >> select >> '2021-06-15'::date as d, >> 'dd-Mon-yyyy TZH:TZM' as fmt) >> select >> rpad(current_setting('timezone'), 20) as "timezone", >> to_char(d, fmt) as "implicit cast to timestamptz", >> to_char(d::timestamptz, fmt) as "explicit cast to timestamptz", >> to_char(d::timestamp, fmt) as "explicit cast to plain >> timestamp" >> from c; >> \t on >> set timezone = 'Europe/Helsinki'; >> execute s; >> set timezone = 'America/Los_Angeles'; >> execute s; >> \t off >> It gives the result that I'd expect: >> Europe/Helsinki | 15-Jun-2021 +03:00 | 15-Jun-2021 +03:00 >> | 15-Jun-2021 +00:00 >> America/Los_Angeles | 15-Jun-2021 -07:00 | 15-Jun-2021 -07:00 >> | 15-Jun-2021 +00:00 >> And, given that nobody would include "TZH:TZM" in the template for rendering >> a date (except in this contrived test), then all three text renderings in >> this test would be identical. >> However, it seems to me that the proper practice must be not to rely on >> intellectual analysis and the implicit cast. Rather, you must say that >> "date" is more like plain "timestamp" than it's like "timestamptz" (in that >> it knows nothing about timezones), and to write the explicit cast to plain >> "timestamp". But this leads to nastily cluttered code. >> *Why is there no "date" overload of "to_char()"?*
You've lost me entirely here, I'm afraid. My question was simple: why is there no "to_char ( date, text ) → text" overload? Without this, and as long as the good practice rule is followed to code so that implicit conversion is never invoked, then using "to_char()" on a "date" value requires writing an explicit typecast. There are only two possible choices: cast to plain "timestamp" or cast to "timestamptz". And for reasons that I'm sure you'd explain better than I would, the choice makes no actual difference to the outcome when a template is used that's natural for a "date" value. So the consequence is that you have to write cluttered code and a fairly elaborate comment to say what your intention is. What would you do here? Would you break the rule of practice so that you simply invoke "to_char()" on a "date" value _without writing a typecast_ and then letting the implicit conversion (which we know is to "timestamptz") have its innocent effect?
