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@xxxxxxxxxxx
