> On 21-Oct-2021, at 17:15, Adrian Klaver <adrian.klaver@xxxxxxxxxxx> 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?
