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.postgresql.org/docs/8.3/release-8-3.html
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.postgresql.org/docs/14/functions-formatting.html
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()"?*
>
--
Adrian Klaver
adrian.klaver@aklaver.com